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. 


Thread Tools  Display Modes 
#1




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




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




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




comparing two spreadsheets using vlookup and compare
The suggestion was based on my best interp* on your example setup (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 reconstructed the setup 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




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




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




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  


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 