Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Help
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Help
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 -- Allllen "Jessinat" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Help
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/assistan...lconPT101.aspx Jessinat wrote: 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. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Help
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. -- Allllen "Jessinat" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Help
Thanks so much for your help.
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Help
Thanks so much for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another way to lookup data | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |