Extracting the nth occurence from an array

In one of the earlier posts, I extracted the first, last, min and max occurence from an array for a particular lookup. Refer this post. The next step perhaps would be to answer if it is possible to extract the second, third occurences and so on. Consequently to extract all occurences for a particular lookup_value. [...]

Read Full Post »

Multiple extraction through vlookup

The problem here is extraction of multiple results from a two column array through a vlookup   The data available is given in columns B and D. The 4 regions along with their major cities are known. The data needs to be arranged as in the table below Start by sorting on region (column B) [...]

Read Full Post »

Identifying duplicates in a list

Tips for identifying duplicates in a list Lets say the list is Tip#1: Conditional formatting Go to conditional formatting box and enter “Formula is” =COUNTIF($A$1:$A$8,A1)>1 Format the box with a different colour (yellow in above) Format paint for all cells A1 to A8 with ‘format painter’ brush Tip#2: Array formula Enter an array formula {=IF(MAX(COUNTIF(A1:A8,A1:A8))>1,”Duplicates”,”No [...]

Read Full Post »