Excel tip – How to get a unique list of items from a table

Filter By Topic

All

excel grouping cellsHave you ever wanted to get a unique list of items, like customers, or accounts from a large list?  I was helping a friend, Sophia, a few days ago and she was manually looking at the list of expenses for the last 12 months and manually copy and pasting all the unique expense categories like Travel NY to DC and Car Rental DC.  With several hundred employees, this was going to take a long time!

So I suggested Sophia use the Excel Advanced Filter to get the list of unique items.  Here is how it works.

  1. Select the Data Ribbon in Excel and select Sort & Filter, Advanced button.
    Advanced Filter
  2. Select Copy to another location.
  3. The List Range needs to be the column of data that has the items you want to get a unique list of. In my friend's case, it was expense category.
  4. Leave Criteria range blank.
  5. Copy to needs to be a cell to the right (can be anywhere) of the data.
  6. Most importantly, check Unique records only.
    Advanced Filter 2
  7. Click on OK and your unique list will be created.

And you're done!

Sophia's next task was to sum the data into the expense categories using Excel functions so she could report that data.  I asked her if she expected more questions like expense type by person, region or date and she said "Probably." With this in mind, we both decided it was smartest to build a CALUMO cube.  This gave her access to her expense data in slice or dice she wanted with all the aggregation points possible.

Sophia is a happy person with plenty more time on her hands again.

If you want more information about CALUMO, click on the Try it Now button on the right.

Leave a Reply

Your email address will not be published.