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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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
 

Dan71

New Member
Joined
Jul 31, 2006
Messages
48
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

New Member
Joined
Jul 31, 2006
Messages
48
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?
 

Forum statistics

Threads
1,136,926
Messages
5,678,605
Members
419,775
Latest member
joh93

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
Top