Lookup Help

[Guest]

I have a spreadsheet where I have data (column A)

800-aaa-nnnn abcde 1
800-aaa-nnnn fghijk 1
800-aaa-nnnn lmnop 1
800-aaa-nnnn qrstuv 11
800-aaa-nnnn wxyz 3
800-bbb-oooo abcde 1
800-bbb-oooo fghijk 4
800-bbb-oooo lmnop 1
800-bbb-oooo qrstuv 1
800-bbb-oooo wxyz 1
800-bbb-oooo aderq 2
866-ccc-pppp fghijk 1
866-ccc-pppp lmnop 1
866-ccc-pppp qrstuv 2
866-ccc-pppp wxyz 2
866-ccc-pppp aderq 2



And I need to be able to summarize the data by Toll Free Number and Type

abcde fghijk lmnop qrstuv wxyz aderq

800-aaa-nnnn
800-bbb-oooo
866-ccc-pppp

I really need help with this, thanks in advance.

 

[Guest]

Jessica,

Your data is neatly structured and I think the best way is a pivot table.
You will need to add headings: Toll Free Number, Type, Qty above your data

Have a look here

http://www.cpearson.com/excel/pivots.htm

 

[Dave Peterson]

It looks like a nice fit for Data|Pivottable.

Add headers to row 1 (if you don't have them)
select the range A1:Cxxx
Data|pivottable
Follow the wizard until you get to a dialog with a Layout button on it.
Click that layout button
drag the header for the phone number to the Row field
drag the header for the type column to the column field
drag the header for the qty(?) column to the data field

And finish up the wizard.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Guest]

Forgot to mention, if you need your data in 3 columns, then
Select column A
use Data > Text to columns
choose delimited
select space delimited
and finish

that puts your data from 1 column into 3, separated where the spaces were.
Now you are ready for the pivot table piece.

 

[Guest]

Thanks so much for your help.

 

[Guest]

Thanks so much for your help.