Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare Old & New Product List (i.e. VLOOKUP) | Excel Worksheet Functions | |||
VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
VLOOKUP 1 stop Answer: 3 steps: count dup's, compare dif's, merg | Excel Discussion (Misc queries) | |||
can i use filters to compare data instead of vlookup? | Excel Discussion (Misc queries) | |||
using VLOOKUP to compare 2 columns to find new information | Excel Worksheet Functions |