Microsoft Excel – How to Quickly Find Repeated Values in a Column
Imagine you have an Excel spreadsheet that contains a list of values that appear in a single column, and lets say this column contains id numbers. It happens that the spreadsheet data was obtained from an invoices list of the previous month, and you are asked to obtain the number of unique customers from that list. If the list contains less than 20 or 30 rows that could be something you can easily do manually just by looking and counting them, but what happens when the list contains 200 or even 3000 rows?
That’s when you have to find a method to let Microsoft Excel do the hard work, and you can achieve that by following the steps in this example:
1. Lets say you have these simple values in column A, starting from row 1 to row 10:
BB
DD
GG
EE
GG
SS
BB
AA
KK
SS
2. First we have to get our data sorted, so we click on the first value (that would be cell A1 in the example), and then go up to the menu item called “Data” and select the option “Sort”.
3. The Sort window should appear, and by default it will show the option “Sort by” and the value “Column A”. Press the OK button. That should have changed our list into this:
AA
BB
BB
DD
EE
GG
GG
KK
SS
SS
4. Go to cell B1 and type the following formula (including the left “=” sign) and press Enter:
=IF(A1=A2, 1, 0)
5. The value in B1 should be “1”, because what the formula is doing is that when the value in cell A1 is equal to value in cell A2 then it should write a “1”, else it should show a “0”. Copy cell B1 (Ctrl+C), and paste it in cells B2 to B10, right next to every value on the list we are using.
6. This is the result we should get:
AA 0
BB 1
BB 0
DD 0
EE 0
GG 1
GG 0
KK 0
SS 1
SS 0
Notice it? Using the IF formula we have found how many repeated values exist in the list, which in this case would be the three cells that have a “1” value next to them.
This method will save you a lot of time and effort, so practice it as much as you can!
Muhammad Aqib 
achsan uddin