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…
Category: Challenges
Challenge 69 – Need Help in Not Showing Comments
Download the workbook. Whenever I select a cell, the yellow box pops up. I need your help to provide me instructions to remove these. The workbook can be downloaded from Challenge 69 – Comments The solution to above challenge will be published after a month i.e. on 20-Feb-2018.
Solution – Challenge 68 – 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…
Challenge 68 – Generate a Particular Sequence
Study the sequence below and you need to write a formula which when dragged down should generate the below sequence. For visual representation purpose, the sequence is in 5 columns but you need to generate the sequence in one column only. You need to generate upto 100 entries. The solution to this challenge will be…
Solution – Challenge 67 – Find Duplicates and Show the Count
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…
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
Challenge 67 – Find Duplicates and Show the Count
You have been given following data and you need to show corresponding count. You need to write a formula which can generate this count. If needed, you can use a maximum of helper column also. Download problem workbook from Find Duplicates and Show the Count The solution to this problem will be published after a…
Challenge 66 – Find the Position of Word
Suppose you have been given a word say "and" and you need to find which word position is this In Mr. and Mrs. Smith – Position of "and" is 2 as this is the 2nd word, the position is not 5. In Samarand Smith and Kittie Smith – the position is 3 not 2 as…
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
Challenge 65 – How Many Sundays on Last Date of Months
This time you need to work out the number of Sundays on last date of the months between two given dates. For example, between two dates of 1-Jan-2017 to 31-Dec-2020, total number of Sundays on last date of the months is 6 as highlighted below. The answer to the solution will be published after a…