Solutions to the Past Challenges

Wed 16 Jan 2019

## Solution - Challenge 68 - Need a Data Validation Formula

By |Wednesday, January 16th, 2019|Categories: Solutions|Tags: |0 Comments

Below is a proposed solution to the Challenge 68 - Need a Data Validation Formula

=IFERROR(IF(ISNUMBER(SEARCH("/",A2)),IF(--RIGHT(A2,4)=2018,AND(--LEFT(A2,2)>=1,
--LEFT(A2,2)<=12),IF(AND(--RIGHT(A2,4)<=2021,
--RIGHT(A2,4)>=2019),AND(--LEFT(A2,2)>=1,--LEFT(A2,2)<=4)))),FALSE)

Tue 20 Feb 2018

## Solution - Challenge 69- Need Help in Not Showing Comments

By |Tuesday, February 20th, 2018|Categories: Solutions|Tags: |0 Comments

Below is a possible solution to Challenge 69 - Need Help in Not Showing Comments

These are not comments but Data Validation Messages..

Select the cell where pop up message is appearing > Data tab > Data Validation > The pop message is in the Input Message > Either you can remove messages or just say clear all.

To remove them in block -
1. Select rows 1 to 7 > Data tab > Data Validation > Clear All
2. Select rows 20 to 22 > Data tab > Data Validation > Clear All

Tue 16 Jan 2018

## Solution - Challenge 68 - Generate a Particular Sequence

By |Tuesday, January 16th, 2018|Categories: Solutions||0 Comments

Below is a possible solution to the problem Challenge 67 - Generate a Particular Sequence -

This consists of 2 logic.

1. Generate Triangular number. The formula for which is n*(n+1)/2
2. Generate Column Labels (A, B, AA, ZZ....) from numbers, the formula for which is

=SUBSTITUTE(ADDRESS(1,3,4),1,"") where 3 is for column C. So, we need to vary this 3.

The combined formula would be -

Sat 09 Dec 2017

## Solution - Challenge 67 - Find Duplicates and Show the Count

By |Saturday, December 09th, 2017|Categories: Solutions|Tags: , , |0 Comments

Below is a possible solution to the problem Challenge 67 - Find Duplicates and Show the Count

Use a helper column in which put following formula -

=LEFT(SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),SUBSTITUTE(A2&"-","-","%")),
" ","%",2),FIND("%",SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),
SUBSTITUTE(A2&"-","-","%"))," ","%",2))-1)

In the last column, put following formula -

=IF(COUNTIF(\$B\$1:B2,B2)=1,COUNTIF(B:B,B2),"")

The solution work can be downloaded from Solution - Find Duplicates and Show the Count

Tue 14 Nov 2017

## Solution - Challenge 66 - Find the Position of Word

By |Tuesday, November 14th, 2017|Categories: Solutions|Tags: , |0 Comments

Below is a proposed solution for the Challenge 66 - Find the Position of Word

Put the following formula and drag down. Let's assume the word "and" in cell D2 -

=LEN(REPLACE(A2,SEARCH(" "&\$D\$2," "&A2),LEN(A2),""))-LEN(SUBSTITUTE(
REPLACE(A2,SEARCH(" "&\$D\$2," "&A2),LEN(A2),"")," ",""))+1

You may download the solution workbook - Solution - Challenge 66 - Find the Position of Word

Tue 10 Oct 2017

## Solution - Challenge 65 - How Many Sundays on Last Date of Months

By |Tuesday, October 10th, 2017|Categories: Solutions|0 Comments

Below is a possible solution to the Challenge 65 - How Many Sundays on Last Date of Months

=SUMPRODUCT((ROW(INDIRECT(\$B\$1&":"&\$B\$2))=EOMONTH(ROW(INDIRECT(
\$B\$1&":"&\$B\$2)),0))*(TEXT(ROW(INDIRECT(\$B\$1&":"&\$B\$2)),"ddd")="Sun"))

Below is an Excel sheet containing the solution

Solution - Challenge 65 - How Many Sundays on Last Date of Months

Tue 15 Aug 2017

## Solution - Challenge 64 - Sum up the Range where a particular alphabet appears

By |Tuesday, August 15th, 2017|Categories: Solutions|Tags: , , |0 Comments

Below is a possible solution to the Challenge 64 - Sum up the Range where a particular alphabet appears

=SUMPRODUCT(ISNUMBER(SEARCH(" "&C2&","," "&A2:A13&","))*(B2:B13))

Mon 10 Jul 2017

## Solution - Challenge 63 - Convert to Date Format

By |Monday, July 10th, 2017|Categories: Solutions|Tags: , , , , , , , , , , , |0 Comments

Below is a possible solution to the Challenge 63 - Convert to Date Format

Put following formula and drag down

=IFERROR(--SUBSTITUTE(A1,",",""),--SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(A1,",","")," ","*",2),"*",", "))

Tue 13 Jun 2017

## Solution 62 - Produce the Sum for Merged Cells Headers

By |Tuesday, June 13th, 2017|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution for the challenge - Challenge 62 - Produce the Sum for Merged Cells Headers

Put following formula B14 and drag right and down

=SUM(OFFSET(\$A\$1,ROWS(\$1:1),MATCH(B\$13,\$1:\$1,0)-1,,IFERROR(MATCH(C\$13,\$1:\$1,0),COUNTA(\$2:\$2)+1)-MATCH(B\$13,\$1:\$1,0)))

Mon 01 May 2017

## Solution - Challenge 61 - Generate Multiplication Table

By |Monday, May 01st, 2017|Categories: Solutions||0 Comments

Below is a possible solution to the challenge - Challenge 61 - Generate Multiplication Table

Put following formula and drag right and down -

=ROWS(\$1:1)*COLUMNS(\$A:A)