comparing two spreadsheets using vlookup and compare

[Sweetetc]

I have two spreadsheets with approx 30 columns of data. They are not
identical I am trying to create a 3rd spreadsheet which will validate if
the mapped data was done correctly. I thought I would have to copy a column
of data from the 1st sheet into the 3rd sheet, then do a lookup of the data
in sheet 1 and sheet - then using somekind of a compare function if the data
is the same return the value of Y
example

Sheet 1

A1 name A2 apple A3Orange A4 Radish
B1 ID B2 001 B3 004 B4 505
C1 place C2 Table C3 bowl C4 Counter

Sheet 2
A1 place A2 counter A3 filler A4 bowl
B1 name B2 Radish B3 filler B4 Orange
C1 ID C2 505 C3 filler C4 004

Sheet 3

Column A would be the ID's
Column B I would want to look up the id in sheet 1 and compare the name
field to the id look up in sheet 2. Iif they were the same return a Y
Thanks
ETC

 

[Max]

For clarity, try a set up along these lines ..

In Sheet3, assuming ids start in A2 down ...

Labels placed in B11 :
From Sheet1, From Sheet2, Comparison Results

Put in B2:
=IF(A2="","",INDEX(Sheet1!A:A,MATCH(A2,Sheet1!B:B,0)))

Put in C2:
=IF(A2="","",INDEX(Sheet2!B:B,MATCH(A2,Sheet2!C:C,0)))

Put in D2:
=IF(A2="","",IF(AND(ISNA(B2),ISNA(C2)),"Names not found in both
sheets",IF(ISNA(B2),"Name not found in Sheet1",IF(ISNA(C2),"Name not found
in Sheet2",IF(B2=C2,"Y","Names found in both sheets but they do not
match")))))

Select B22, copy down as far as required. Col D will return a richer
diagnostic, which if required, can be easily verified by looking at the
corresponding returns in cols B & C.

 

[Sweetetc]

Max

I have 31 columns on spreadsheet 1 to compare with 52 columns in spread
sheet 2
Is your formula realistic to just get a quick snapshot of whether the
mapping matches? If I understood what you are saying I would need to return
the values for sheet 1 column B and sheet 2 column C and then compare them in
column D. That would work but it would be unmanagable as I have over 9000
lines of data

 

[Max]

The suggestion was based on my best interp* on your example set-up (as
posted) for Sheets 1 and 2 which was taken to be representative. If it is
representative, then the topline formulas in B22 in Sheet3 can simply be
copied down 9,000 rows as mentioned.

*I re-constructed the set-up based on your cell refs as posted viz:
(assumed "filler" meant blank cell)

Sheet 1

A1 name A2 apple A3Orange A4 Radish
B1 ID B2 001 B3 004 B4 505
C1 place C2 Table C3 bowl C4 Counter

Sheet 2
A1 place A2 counter A3 filler A4 bowl
B1 name B2 Radish B3 filler B4 Orange
C1 ID C2 505 C3 filler C4 004

This was my interp of the above:

In Sheet1 (cols A to C):

name id place
apple 001 table
orange 002 bowl
radish 505 counter

In Sheet2 (cols A to C):

place name id
counter radish 505

bowl orange 004

 

[Sweetetc]

Max

You have correctly interpreted the spreadsheet. I agree this will work. I
was hoping to create a 3rd spreadsheet of just results Y or N by ids without
moving the data to the 3rd spread sheet. Do you know of any way to do that?

 

[Max]

Try this revised approach which still uses a new Sheet3 to compare the source
Sheets 1 and 2 while catering for the different col placements in both source
sheets ..

A sample construct is available at:
Comparing 2 sheets with different col placements.xls
http://www.savefile.com/files/68553

In Sheet3:

Paste the uniques list of ids in A2 down. Use Data > Filter > Advanced
Filter, unique records only to generate this list. Do this separately in
another sheet. Just copy and paste the id ranges from both sheets 1 and 2
into a single col A, one below the other, then do the Data > Filter >
Advanced Filter, copy to another location: B1, check "Unique records only" >
OK.

Paste the list of complete* col headers from say, Sheet1 in B1 across
*assuming both Sheets 1 and 2 have complete and identical** col headers,
except that the cols are placed differently in both sheets
**ie matching col headers/labels

Then place in B2:
=IF(OR($A2="",B$1=""),"",IF(OR(ISNA(MATCH($A2,Sheet1!$B:$B,0)),ISNA(MATCH($A2,Sheet2!$C:$C,0))),"",IF(INDEX(OFFSET(Sheet1!$A:$A,,MATCH(B$1,Sheet1!$1:$1,0)-1),MATCH($A2,Sheet1!$B:$B,0))=INDEX(OFFSET(Sheet2!$A:$A,,MATCH(B$1,Sheet2!$1:$1,0)-1),MATCH($A2,Sheet2!$C:$C,0)),"Y","N")))
Copy B2 across and fill down to populate the table

The above should return the required results. The revised formula reads the
col headers in both source sheets (which are not identically structured in
terms of col placements) to determine the correct cols to index & compare for
the ids listed in col A.

 

[Max]

I've posted a response* to this earlier, re:
http://makeashorterlink.com/?F371423CD

*a revised approach which still uses a new Sheet3 to compare the source
Sheets 1 and 2 while catering for the different col placements in both
source
sheets

The sample construct (full details) is available at:
http://www.savefile.com/files/68553
Comparing 2 sheets with different col placements.xls