# Sort Data

#### froggiebeckie

##### Board Regular
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

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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

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

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

Thanks again,

BeckieO

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

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?

Replies
2
Views
242
Replies
1
Views
404
Replies
5
Views
120
Replies
6
Views
528
Replies
5
Views
156

1,217,383
Messages
6,136,245
Members
450,001
Latest member
KWeekley08

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back