Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter/ combine data
I have data that was exported to excel then filtered into a list of stores
that have ordered from us over the past year. I now need to match that list against a list of all stores and thier addresses so that i end up with a list of addresses for the stores that have ordered from us in the last year. Here is what it looks like Workbook1 Workbook2 Store # Store # Address 001 001 123 Main Street 003 002 465 Main Street 007 003 1 State Street How do I take the information in workbook 2 and make it only show the stores listed in workbook 1 without going through and hiding each store that I do not want to see. if i hide them individually it will take all day. I am working with Excel 2007. Please help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter/ combine data
Why not use index/match to retrieve it directly?
Assuming both Book1.xls and Book2.xls are open at the same time, with data assumed within Sheet1 in cols A and B, from row2 down (the source Book2.xls contains Store#'s in col A and Addresses in col B) In Book1, Store#'s are assumed in A2 down Put in B2: =INDEX([Book2]Sheet1!$B:$B,MATCH(A2,[Book2]Sheet1!$A:$A,0)) Copy down to retrieve the addresses from Book2's col B In the above, unmatched cases will return "ugly" #N/As If you want it to return neat looking blanks: "" instead for any unmatched cases, use this in B2, copy down: =IF(ISNA(MATCH(A2,[Book2]Sheet1!$A:$A,0)),"", INDEX([Book2]Sheet1!$B:$B,MATCH(A2,[Book2]Sheet1!$A:$A,0))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Rubystarx" wrote: I have data that was exported to excel then filtered into a list of stores that have ordered from us over the past year. I now need to match that list against a list of all stores and thier addresses so that i end up with a list of addresses for the stores that have ordered from us in the last year. Here is what it looks like Workbook1 Workbook2 Store # Store # Address 001 001 123 Main Street 003 002 465 Main Street 007 003 1 State Street How do I take the information in workbook 2 and make it only show the stores listed in workbook 1 without going through and hiding each store that I do not want to see. if i hide them individually it will take all day. I am working with Excel 2007. Please help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine data | Excel Worksheet Functions | |||
in data/filter/auto filter | Excel Worksheet Functions | |||
Combine Data | Excel Discussion (Misc queries) | |||
Improve autofilter combine conditionals with filter criteria | Excel Worksheet Functions | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |