Extracting Unique, Duplicate and Missing Items using Formulas [spreadcheats]

Often I wish Microsoft had spent the effort and time on a data genie (and a set of powerful formulas) that can automate common data cleanup tasks like extracting duplicates, makings lists unique, find missing items, remove spaces etc. Alas, instead they have provided features like clippy which are intrusive to say the least.

So as part of our second installment of spreadcheats we will learn how to tackle few of the most common data processing tasks:

Getting Unique Items from a List of Cells

There are 3 simple ways to do this:

1. Using Advanced Data Filter
2. Using countif() and auto filter
3. Using formulas as described here

Assuming you have data as shown in the picture aside (and wishing you will have customers like those):

• First add a column to the left of the list. Here we will use formulas to fill numbers based on the uniqueness of the cell next to it.
• Essentially our formula should generate numbers in increasing order as long as the corresponding item is unique and not increase the number otherwise.
• So the formula for order column can be like this: =IF(COUNTIF(list-upto-that-point, current element)=1,previous-order+1, previous-order)
  See the example below:
  
  remember, the first cell order is 1.
• See how we are using both absolute and relative references to fetch the counts.
• Now add another column to the right of the list, here we will fetch unique items.
• We will use vlookup() to fetch each of the 12 unique items. The formula goes like this:
  =VLOOKUP(running number,$B$4:$C$22,2,FALSE)
  You can wrap the vlookup() with if() formula to avoid seeing #value errors.

That is all. Using this method you can extract unique items froma list.

Eliminating Doubles from a List

There are 2 ways in which you can find and remove duplicates(doubles) in excel lists with ease:

1. Using countif() and then auto-filter
2. Using formulas

The process for finding duplicates using formulas is same as that of finding unique items.

Instead of writing COUNTIF(list-upto-that-point, current element)=1, we now write COUNTIF(list-upto-that-point, current element)=2. Also the first element’s count should be changed to zero.

Once done the list should look like what you see on the side.

Finding Missing Items by comparing one list with another:

Even though this might seem like a different challenge, it is infact same as the above techniques. You need to use countif() to compare first list’s elements with second list. How? that is your home work.

Download and see these formulas in action:

Still having some doubts? Download the excel tutorial – unique & duplicate items and learn by poking around.