Reduce a list of multiple entries to a single list

**Jogier505** · 03-24-2011, 06:25 PM

I have a data set of over 2200 entries, most of which are multiple entries. I am trying to reduce them to a single list of unique entries so I can isolate specific data. I think the attached file will make it much clearer.

Jim O

**Cutter** · 03-24-2011, 07:14 PM

Here is one way:

I added formulas in Col N on the All Data sheet. For ease and simplicity I referred those formulas to your existing results in Col M but that isn't necessary. Col M could be eliminated and formulas in N modified to do calc that Col M is doing. I have left the N/A's in your existing formulas on the List sheet as I'm sure you'll know how to handle those.

**Jogier505** · 03-24-2011, 07:37 PM

Cutter,

That is not quite what I am looking for. In the file I provided there should be a total of 11 different combinations of MFG, Model and Flex. Column "A" on the "All Data" sheet becomes a unique value and I want to list each combination. Column "M" breaks out the number of entries for each value and I want to produce a list based on that number.

Jim O

**Cutter** · 03-24-2011, 07:39 PM

I just realized I goofed up the formulas in column N.

Here is the revised version:

**martindwilson** · 03-24-2011, 07:55 PM

use advanced filter to extract an unique list

**Jogier505** · 03-24-2011, 08:05 PM

Cutter,

That looks like it will work. I will apply it to my data set tomorrow and give it a try.

For now I think I can mark this Solved
.
martindwilson,

As data will be added on an ongoing process, I wanted an easier way to accept new data rather than run a filter everytime I entered something.

Thank you both for your time and input.

Jim O

**scottylad2** · 03-24-2011, 09:39 PM

I know this is solved, but always good to let a big gun out of the bag every now and again. Called column A in your All Data tab List, hadn't spotted there's a tab called that as well.....it's late!
needs entered with Ctrl + Shift + Enter

=IF(ROWS(A$2:A2)>SUMPRODUCT(--(List<>"")/COUNTIF(List,List&"")),"",INDEX(List,SMALL(IF(FREQUENCY(IF(List<>"",MATCH(List,List&"",0)),ROW(List)-ROW('All Data'!$A$2)+1),ROW(List)-ROW('All Data'!$A$2)+1),ROWS(A$2:A2))))