Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Matching
Hello,
I have a question. I have two sets of data - one large and one small. The large one has metadata, and the small one fits entirely inside the large one. I need to separate out the metadata from the large set for just the entries in the small set. For example, say I have these three columns: abc 123 123 bcd 234 567 cde 456 678 def 567 901 efg 678 012 fgh 789 ghi 890 hij 901 ijk 012 jkl 321 klm 432 Where column A is the metadata, column B is the large set, and column C is the small set. I want to create another column (or two columns) that match the data in column C with that in column A. So the output that I want looks like this: abc 123 def 567 efg 678 hij 901 ijk 012 In other words, I want the metadata from column A to match with the data in column C. I can't just do a sort, because column C is missing some numbers. Does anyone have any thoughts for how to do this? Thanks for your help, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Matching
Demosthenes -
If you can switch the order of columns A and B, then a simple VLOOKUP will do what you want. Assuming data starts in row 2, in cell D2, you would have: VLOOKUP(C2, A$2:B$12,2,FALSE) Change the B12 to be the last cell in the column B, and then copy/paste or drag this formula down for all the values in column C. (If you can't change the order of columns A and B, then copy/paste them somewhere else with the new order, and base your VLOOKUP on the copied cells) -- Daryl S "Demosthenes" wrote: Hello, I have a question. I have two sets of data - one large and one small. The large one has metadata, and the small one fits entirely inside the large one. I need to separate out the metadata from the large set for just the entries in the small set. For example, say I have these three columns: abc 123 123 bcd 234 567 cde 456 678 def 567 901 efg 678 012 fgh 789 ghi 890 hij 901 ijk 012 jkl 321 klm 432 Where column A is the metadata, column B is the large set, and column C is the small set. I want to create another column (or two columns) that match the data in column C with that in column A. So the output that I want looks like this: abc 123 def 567 efg 678 hij 901 ijk 012 In other words, I want the metadata from column A to match with the data in column C. I can't just do a sort, because column C is missing some numbers. Does anyone have any thoughts for how to do this? Thanks for your help, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Matching
Try index/match. Its more versatile - match on any col, return any other col
to the left/right of the match col Assume source data as you posted is in A2:C2 down In D2: =IF(ISNA(MATCH(C2,B:B,0)),"",INDEX(A:A,MATCH(C2,B: B,0))) In E2: =IF(ISNA(MATCH(C2,B:B,0)),"",C2) Copy D2:E2 down. voila? celebrate it, hit the YES below -- Max Singapore --- "Demosthenes" wrote: I have a question. I have two sets of data - one large and one small. The large one has metadata, and the small one fits entirely inside the large one. I need to separate out the metadata from the large set for just the entries in the small set. For example, say I have these three columns: abc 123 123 bcd 234 567 cde 456 678 def 567 901 efg 678 012 fgh 789 ghi 890 hij 901 ijk 012 jkl 321 klm 432 Where column A is the metadata, column B is the large set, and column C is the small set. I want to create another column (or two columns) that match the data in column C with that in column A. So the output that I want looks like this: abc 123 def 567 efg 678 hij 901 ijk 012 In other words, I want the metadata from column A to match with the data in column C. I can't just do a sort, because column C is missing some numbers. Does anyone have any thoughts for how to do this? Thanks for your help, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enter Data on one tab, then find matching data on another tab | Excel Worksheet Functions | |||
Matching a column of new data to existing larger data set. | Excel Worksheet Functions | |||
Matching identical data using data only once in the matching proce | Excel Discussion (Misc queries) | |||
Can I merge data in 2 sheets matching rows of data by last name? | Excel Discussion (Misc queries) | |||
Matching data and linking it to the matching cell | Links and Linking in Excel |