Excel 2007 : How to limit a list to first of each group

**dweit** · 08-15-2011, 10:41 AM

I have a spreadsheet with a list of product stock #'s and descriptions. The product comes in sizes (shoe sizes), so there is a row for each product/size - for instance "m's adirondack blk 08.0, m's adirondack blk 08.5, m's adirondack blk 09.0" etc. This makes for a very long spreadheet, and I really only need a list of each style that it contains - "m's adirondack blk", not each size, thereby cutting the list for each style from 10 or 12 rows, to 1.
I have a feeling a pivot table could work, but how do I get it to show only the first of each product set? FYI, the stock # column for the example above would look something like "ABC12345-08.0, ABC12345-08.5, ABC12345-09.0, etc."

Any help would be appreciated!

**NBVC** · 08-15-2011, 10:46 AM

If you can mark the row's of interest by inserting a value in an empty column for each row, then you can use autofilter to view only those rows that are marked.

You can then also, if desired, copy and paste just those rows elsewhere.

**dweit** · 08-15-2011, 11:05 AM

That's not a bad idea, NBVC, thanks. Problem is it is a longish spreadsheet (over 1000 rows) and it would be pretty labor intensive to go through and mark each row where the style changes for later filtering, and also subject to user error by missing a style change. I was hoping for some type of macro maybe that could automate the process. But your suggestion is definetly doable for us - thanks again.

**NBVC** · 08-15-2011, 11:18 AM

You can possibly automatically mark the rows by using a countif formula...

e.g..

=COUNTIF($A$2:$A2,$A2)=1

copied down

This counts number of times the value in A exists from top of column to current row.... if that number is 1 (i.e. first occurance) then TRUE results.

Adjust to column of interest (one that shows duplication that you want to limit).

then you can copy the column and paste special >> Values over itself, and filter for TRUE.