VLOOKUP function to lookup more than 1 cell in a column
Posted by QUI on September 06, 2001 3:35 AM
I've got a problem. I have a sheet "History" which contains all the data of orders customers made in the past. Now I want that in sheet "All Orders" all the orders made by one customer are visible so I think that's possible with the VLOOKUP function but it's possible that it's looking for example for: "Customer A" and that Customer A is in cell "A1", "A3", "A8", etc. so it must look for all those cells and not only the first match but all the matches in this column. Hope someone can help me? Thank you!
QUI
Posted by Eric on September 06, 2001 6:23 AM
Have you looked into using a pivot table?
Pivot table seems like the ideal solution if your data set is not too large, and you need to update the "all orders" sheet frequently.
You could also autofilter based on the "customer" column, but that's fairly cumbersome. Probably useful if you make an "all orders" set at the end of the year- marginally more useful than just sorting on the "customer" column.
Any of those ideas useful?
Posted by QUE on September 06, 2001 6:42 AM
Re: Have you looked into using a pivot table?
Can you tell me more about using the pivot table? How will I do this? I really have no idea. Thank you!
Que
Posted by Eric on September 06, 2001 10:11 AM
basic pivot table scenario
If you have col(A) for customers and col(B) for "orders", then select colA and B, then
Data-->Pivot Table and Pivot Chart Report and the pivot chart wizard comes up.
select "microsoft excel list or database" and "pivot table" on the first screen,
click "OK"
on the next screen make sure that the range you selected is correct and click "next"
on the next screen you can choose from a new worksheet or within this worksheet for pivot table creation. Click the "layout" button and drag the "customer" button over to the "row" area and the "order" button to the "column" area. also drag the "order" button to the "data" area (the default operator here is count, which may be what you want here). Click "OK" and click "finish"
The new pivot table should list each unique customer down its first column, and each unique order across the top row, at the intersection of each column and row within the table will be the number of times that order has been placed for that customer.
Hope that helps
Posted by Eric on September 06, 2001 12:18 PM
Couple of informative sites
Try this link for a UCLA document teaching staffers how to use a pivot table
ftp://www.deans.medsch.ucla.edu/pub/Manuals/pivot.doc
and this from the Denver business Journal (I found it through "MathTools.net"
http://denver.bcentral.com/denver/stories/1996/10/07/smallb4.html
