A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

comparing two spreadsheets using vlookup and compare



 
 
Thread Tools Display Modes
  #1  
Old September 12th 06, 08:13 PM posted to microsoft.public.excel.worksheet.functions
Sweetetc
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
Ads
  #2  
Old September 13th 06, 02:45 AM posted to microsoft.public.excel.worksheet.functions
Max
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 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.
--
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  
Old September 13th 06, 03:08 AM posted to microsoft.public.excel.worksheet.functions
Sweetetc
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 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.
> --
> 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  
Old September 13th 06, 03:30 AM posted to microsoft.public.excel.worksheet.functions
Max
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 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

--
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  
Old September 13th 06, 12:01 PM posted to microsoft.public.excel.worksheet.functions
Sweetetc
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  
Old September 14th 06, 05:42 AM posted to microsoft.public.excel.worksheet.functions
Max
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  
Old September 14th 06, 08:25 AM posted to microsoft.public.excel.worksheet.functions
Max
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 01:26 PM
can i use filters to compare data instead of vlookup? Vindell Excel Discussion (Misc queries) 1 January 3rd 06 07: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 10:05 AM.


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