View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sweetetc Sweetetc is offline
external usenet poster
 
Posts: 12
Default comparing two spreadsheets using vlookup and compare

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
--
Thanks
ETC


"Max" wrote:

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

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

Labels placed in B1:D1 :
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 B2:D2, 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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sweetetc" wrote in message
...
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