Ask the Master: Unique List from Repeated Data

BostonAnalyst asks:

I have a list of product sales data that I need to summarize once a week, but the products in the list change from week to week. There are hundreds of products and it takes me an hour to type up a list with each product listed just once so I can run ‘SUMIF’ and average formulas.

Is there an easier way?

There is always an easier way, BostonAnalyst. Almost any tedious task can be solved with macros or VBA programming. But it’s better to avoid programming if you can. The simplest solution is always the best. And fortunately, there is a non-progamming solution to this problem.

1. Open a new Worksheet
2. Paste your list of products in ‘A1’ and sort the data.
3. In cell ‘B1’ use this formula: =COUNTIF($A$1:A1,A1)
4. Copy the formula all the way down to the bottom of your data
5. Copy column B and paste as values in the same column
6. Sort columns A:B by column B.

You now have your unique list of data. This is a simple solution that requires no programming and can be done in a few minutes no matter how much data you have.

If you’re interested in a more comprehensive solution, we can discuss building a custom-made reporting tool. Your weekly summary report can then be generated with the click of a button and have a more professional look to it.