Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default comparing two spreadsheets using vlookup and compare

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default comparing two spreadsheets using vlookup and compare

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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default comparing two spreadsheets using vlookup and compare

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 B2:D2 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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sweetetc" wrote:
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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default comparing two spreadsheets using vlookup and compare

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


"Sweetetc" wrote:

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default comparing two spreadsheets using vlookup and compare

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,Shee t1!$B:$B,0)),ISNA(MATCH($A2,Sheet2!$C:$C,0))),"",I F(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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sweetetc" wrote:
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?
--
Thanks
ETC

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default comparing two spreadsheets using vlookup and compare

I've posted a response* to this earlier,
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sweetetc" wrote in message
...
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?
--
Thanks
ETC


"Sweetetc" wrote:

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare Old & New Product List (i.e. VLOOKUP) Hal Excel Worksheet Functions 3 July 6th 06 06:25 PM
VLOOKUP should compare numbers stored as text to plain numbers. VLOOKUP - Numbers stored as text Excel Worksheet Functions 0 March 31st 06 05:53 PM
VLOOKUP 1 stop Answer: 3 steps: count dup's, compare dif's, merg nastech Excel Discussion (Misc queries) 2 February 12th 06 02:26 PM
can i use filters to compare data instead of vlookup? Vindell Excel Discussion (Misc queries) 1 January 3rd 06 08:53 PM
using VLOOKUP to compare 2 columns to find new information Jessica Excel Worksheet Functions 3 July 19th 05 02:59 AM


All times are GMT +1. The time now is 12:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"