Solutions to the Past Challenges

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

Below is a possible solution to the problem **Challenge 70 - Find Duplicates and Show the Cou**nt

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 - Challenge 70 - Find Duplicates and Show the Count**

## Solution - Challenge 67 - Generate a Particular Sequence

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

This consists of 2 logic.

- Generate Triangular number. The formula for which is n*(n+1)/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 -

=SUBSTITUTE(ADDRESS(1,(ROW(1:1)-1)*ROW(1:1)/2+1,4),1,"")

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

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**

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

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

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

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))

## Solution - Challenge 63 - Convert to Date Format

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),"*",", "))

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

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)))

## Solution - Challenge 61 - Generate Multiplication Table

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)

## Solution - Challenge 60 - Generate a list of 2nd and 4th Saturdays for a Year

Below is a possible solution to the challenge - **Challenge 60 - Generate a list of 2nd and 4th Saturdays for a Year**

Drag the below formula down

=IF(ROWS($1:1)<25,FLOOR(DATE($A$1,ROUNDUP(ROWS($1:1)/2,0),

2*(MOD(ROWS($1:1)-1,2)+1)*7),7),"")

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

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

Below is a possible solution to

Challenge 69 - Need Help in Not Showing CommentsThese 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