With the new Excel 2007 this is really easy and convenient, but I also show you how if you are using an old form of Excel. With this formula strategy you can handle 66,000 rows of records (or more) and get the unique records in under 2 minutes.
So say I have a list of sales people – and I used to have this situation all the time where I’d have a column of data that I just needed the unique values out of. So I am giving you an example here with nine, or eight, records, but I have had scenarios where it is 50,000 rows and I just need the unique sales orders or the unique parts numbers or the unique customers or, in this case, the unique sales people. If you are using the new Excel, you have a very simple way to do this. You can hit the data ribbon and go over to “remove duplicates.” When you click on “remove duplicates,” it prompts you and asks if you have data headers and selects the area that you have selected and confirms that. Once you hit “okay” you get a message that says, “It found 2 duplicates out of there and there are 6 remaining unique values.” Hit “okay” and you are done. Very, very handy.
But what happens if you don’t have that function because you are in the old Excel? Well, what I used to have to do was something like this – I would take the column and I would sort it. So now you can see that “Larry” and “Larry” and “Janet” and “Janet” are right next to each other. And then I would write an “if” statement. My “if” statement would say something like “If this cell is equal to the cell below it – if that’s so than I want it to put in a one. Otherwise, put in nothing.” Then I close the bracket. I take this formula and carry it all the way down. And lo and behold there’s my Janet and there’s my Larry and those are duplicates so I can remove those. Now remember, when you have 10,000 rows, you can’t just go and single spot each one of these. What you have to do is just copy the formula and then paste special where you just paste in the values. When you do that, you remove your formula and then you have another column that you can sort by. So what I would do is highlight both columns (over to this one) and I would sort by this field and then I know that I could remove all of the ones and that would leave me just the unique values.