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…
Tips & Tricks 167 – Shortcut for Refresh All Pivot Tables Not Working
To refresh a Pivot table, the shortcut is ALT+F5. If you want to refresh all Pivots in the workbook, the shortcut is CTRL+ALT+F5. This magical shortcut would do the trick and you don't need any macro to perform this task. But on most of the computer this shortcut may not work. The reason is most…
Excel Quiz 39
VBA – Create a Clickable Index (Summary, Table of Contents) Sheet Macro
Below is a Macro which inserts a sheet named "Index" as first sheet and puts down all the sheets names which are clickable.
Tips & Tricks 166 – Convert a Number to Weekday Name
Suppose you want to return 1 = Sunday, 2 = Monday…..7 = Saturday =TEXT(A1&"Jan2017","dddd") To show only 3 characters of the Weekday Name =TEXT(A1&"Jan2017","ddd") You can add a number to A1 if you want to show some other Weekday Name Say, if you want to show 1 = Monday, 2 = Tuesday…….7 = Sunday, just…
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
VBA – Function to Validate IP Addresss
Below is a function which will return True or False if a valid IP address is entered. A valid IP address is of the form nnn.nnn.nnn.nnn where nnn >=0 and <=255 You can put following type of construct in a cell =IsValidIP(A2)
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…
Tips & Tricks 165 – Convert Weekday Names to Numbers
Suppose Cell A2 contains weekday names like Sunday, Monday…..(or Sun, Mon…), then following formula can be used to return the numbers. Sunday will be 1 and Saturday will be 7. =ROUND(SEARCH(LEFT(A2,2),"SuMoTuWeThFrSa")/2,0) =MATCH(LEFT(A2,2),{"Su","Mo","Tu","We","Th","Fr","Sa"},0) If we want to return some other number to weekdays, then formula can be tweaked accordingly. For example, to make Mon = 1…
VBA – Macro to Clean Non-printable characters
Sometimes, your data contains some characters which make some portion of your data unusable. Below is a macro which cleans your worksheet in the following way – It will remove non-printable characters with ASCII codes 0 to 31. It will remove leading and trailing blanks. Will remove characters with ASCII codes 127, 129, 141, 143,…