Sort Data

[froggiebeckie]

Hi, all.
I've got records in a spreadsheet that include multiple entries for the same product.
For example: (5 records, 3 columns of data each)

Type 1, 120, 1/1/06
Type 2, 100, 1/9/06
Type 1, 50, 1/9/06
Type 3, 75, 1/8/06
Type 2, 100, 1/7/06

I'd like to be able to use SUMIF to calculate some averages, but first I need to list the different types of product listed in column 1. There are hundreds of records and may be as many as 150 different "Types", all mixed in together.

Is there a simple way (formula) to make a list of the different entries in column 1?

What about using a pivot table?

Thanks so much,
BeckieO

 

[pgc01]

Hi BeckieO

To build a list of the different entries you can use Advanced Filter to copy the column, with the option Unique Records Only.

Hope this helps
PGC

 

[Datsmart]

You can build a quick list of unique items from your data.

1. Select the column of data.
2. Data | Filter | Advanced Filter
3. OK to remove Dialog box
4. Check "Copy to another location" | Enter a cell reference for the location of the start of the new list
5. Check "Unique records only" | OK

 

[froggiebeckie]

Bless Yall--I'd completely overlooked that one.

Thanks again,

BeckieO

 

[Dan71]

Yes, a piviot table is the way to go. Use the wizard, put the column A stuff in the column part of the piv and then anything in the data part. This will then shgow you the unique values. Caopy and paste special values out and then use that for your avergeing. You can use =average(if( as an array formular, I'm sure someone will paste a link to explaine how.

Dan

 

[Dan71]

Just tried the advanced filter method, didn't know about it. It seems a bit slow. On a 40K row spreadsheet it is taking ages, the piviot table is instant. Am I missing something?