Sort Data

froggiebeckie

Board Regular
Joined
Sep 27, 2002
Messages
87
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top