Urgent! Match two sheets data together by a common cell??
Posted by Diana on January 25, 2001 7:20 AM
Hi - I really need your help. I have been trying for weeks to solve this problem.
In a nutshell. We have two files that contain people and their demographic data. Both sheets have a common column (id code) for each person. But the people are not the same on each sheet (many will overlap but not all).
How do I considate both sheets to list all the person's information in one row by matching the id code on each sheet?
ie - Sheet 1 has #1 Jane Smith Address
Sheet 2 has #1 Jane Smith Phone Date of Birth
I want Sheet 3 to have #1 Jane Smith Address, Phone, DOB
Thanks!
Posted by cpod on January 25, 2001 10:34 AM
Have you tried a simple lookup function?
Posted by Diana on January 26, 2001 5:54 AM
I dont think that will work. One sheet has lots of data and so does the other. They both need to be merged without losing any. I looked up lookup and it seems more like a matching feature than a consolidating feature. Does that make sense?
Posted by Aladin Akyurek on January 26, 2001 5:01 PM
A possible recipe with VLOOKUP:
1) Insert a column before the first column on Sheet1. Create a series with AutoFill which starts with, say A-1001, A-1002.
2) Go to an empty cell and type =counta(A:A) where I assume that the series is created in column A. Name the cell with COUNTA-formula ACOUNT via the Name Box.
3) Select all the data (excluding labels) on Sheet1 and name it ADATA via the Name Box.
4) Go to Sheet2, again insert a column before the first column of data and create with AutoFill a series which starts with the pair B-1001, B-1002.
5) Select all the data (as in Step 4) on Sheet2 and name it BDATA. Select again all data excluding the series column and name this selection XDATA.
6) Go to Sheet3. Create a series just like the one on Sheet1 in column A. Type
B1 =VLOOKUP(A1,ADATA,2,0) [ copy down as far as needed to get the first column of data and ]
C1 =VLOOKUP(A1,ADATA,3,0) [ copy down as far as needed to get the 2nd column of data ]
If there are more columns on Sheet1, use D1, E1, etc in a similar way.
Supposing that G1 is empty (ADATA is exhausted), type in
G1 = IF(ISNA(VLOOKUP(B1,XDATA, 1,0)),"",VLOOKUP(B1,XDATA,*,0)) where * is a column in the lookup table XDATA that, if applicable, contains additional data. If there can be more use H1, I1, etc.
7) Go to the last empty cell in column A on sheet3 and create a series identical to the one on sheet2. Let's suppose that B-1001 is in A546. Type in
B546 =IF(ISNA(VLOOKUP(VLOOKUP(A546,BDATA,2,0),$B$1:$B$546,1,0)),VLOOKUP(A546,BDATA,2,0),"")
C546 =IF(LEN(B546)>0,VLOOKUP(A546,BDATA,3,0),"")
D546 =IF(LEN(B546)>0,VLOOKUP(A546,BDATA,4,0),"")
and so on to get all columns of data, if they exist.
Copy down the formulas as far as needed.
Select everything on Sheet3, go to Sheet4 and activate Paste Special, Values. Delete all rows on Sheet4, which do not contain data.
Note. I did not try out this recipe, merely wrote it down as a thought experiment. It may thus contain errors in formulas. But, if the recipe makes sense to you, you should be able to sort out such slips.
Aladin
