Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Showing the Differences
I am sure this must be simple but I can't figure it out. I have a web import
on sheet "Import" and a copied version of the old Import on sheet "Old Import". I would like to have another sheet that shows the difference if any between the two with respect to column "Q" in each so that I can monitor any recent changes between the two. Please tell me what I should do??? -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Showing the Differences
When comparing two different worksheets depending on the data depends on how you would do the comparison 1) The simple Case - the two worksheets always have the save number of rows and you are comparing the data in the same row on each sheet. Here you can us conditional formating to compare the two sheets. Use a formula like this Sheet1!Q1 : put formula =Sheet2!Q1 Sheet2!Q1 : put formula =Sheet1!Q1 2) If your data has different number of rows or the data is in a different order then you need a unique ID number so you can compare the two sheets.. Again you can use conditional formating but us a vlookup for comparison Assume a Unique ID is in column A.Vlookup will return NA is the ID isn't found Sheet1!Q1 : put formula =NOT(ISNA(VLOOKUP(A1,Sheet2!A1:Q10,17))) Sheet2!Q1 : put formula =NOT(ISNA(VLOOKUP(A1,Sheet1!A1:Q10,17))) If you want to compare values Sheet1!Q1 : put formula =Q1=VLOOKUP(A1,Sheet2!A1:Q10,17))) Sheet2!Q1 : put formula =Q1=VLOOKUP(A1,Sheet1!A1:Q10,17))) 3) If you have different number of rows and don't have an ID column then you are out of luck. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=151080 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Showing the Differences
On sheet3!Q1 I have put =Sheet1!Q1 to copy over the referencing column. From
there what formula do I need so to only show a value if there is a change between Sheet1 & Sheet2 for the unique ID in column Q, and if so then show the difference. Like if Sheet1 says "10" and Sheet2 says "3" then it will show the difference in Sheet3 of "7". "joel" wrote: When comparing two different worksheets depending on the data depends on how you would do the comparison 1) The simple Case - the two worksheets always have the save number of rows and you are comparing the data in the same row on each sheet. Here you can us conditional formating to compare the two sheets. Use a formula like this Sheet1!Q1 : put formula =Sheet2!Q1 Sheet2!Q1 : put formula =Sheet1!Q1 2) If your data has different number of rows or the data is in a different order then you need a unique ID number so you can compare the two sheets.. Again you can use conditional formating but us a vlookup for comparison Assume a Unique ID is in column A.Vlookup will return NA is the ID isn't found Sheet1!Q1 : put formula =NOT(ISNA(VLOOKUP(A1,Sheet2!A1:Q10,17))) Sheet2!Q1 : put formula =NOT(ISNA(VLOOKUP(A1,Sheet1!A1:Q10,17))) If you want to compare values Sheet1!Q1 : put formula =Q1=VLOOKUP(A1,Sheet2!A1:Q10,17))) Sheet2!Q1 : put formula =Q1=VLOOKUP(A1,Sheet1!A1:Q10,17))) 3) If you have different number of rows and don't have an ID column then you are out of luck. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=151080 Microsoft Office Help . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Showing the Differences
For conditional for mating to work you can produce a true of false results like this =Sheet1!Q1<Sheet2!Q1 If you want something other than true or FALSE THEN USE AN if =IF(Sheet1!Q1=Sheet2!Q1,"Match", "Don't Match") -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=151080 Microsoft Office Help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Showing the Differences
I need to compare two worksheets -- duplicate data entry -- and find only the
cases where the two worksheets don't match. The simple case below would seem to apply, but I'm not familiar with conditional formatting. Could someone walk me through this? Where exactly do I enter that formula? Thanks, teri "joel" wrote: When comparing two different worksheets depending on the data depends on how you would do the comparison 1) The simple Case - the two worksheets always have the save number of rows and you are comparing the data in the same row on each sheet. Here you can us conditional formating to compare the two sheets. Use a formula like this Sheet1!Q1 : put formula =Sheet2!Q1 Sheet2!Q1 : put formula =Sheet1!Q1 2) If your data has different number of rows or the data is in a different order then you need a unique ID number so you can compare the two sheets.. Again you can use conditional formating but us a vlookup for comparison Assume a Unique ID is in column A.Vlookup will return NA is the ID isn't found Sheet1!Q1 : put formula =NOT(ISNA(VLOOKUP(A1,Sheet2!A1:Q10,17))) Sheet2!Q1 : put formula =NOT(ISNA(VLOOKUP(A1,Sheet1!A1:Q10,17))) If you want to compare values Sheet1!Q1 : put formula =Q1=VLOOKUP(A1,Sheet2!A1:Q10,17))) Sheet2!Q1 : put formula =Q1=VLOOKUP(A1,Sheet1!A1:Q10,17))) 3) If you have different number of rows and don't have an ID column then you are out of luck. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=151080 Microsoft Office Help . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
differences between 3 | Excel Discussion (Misc queries) | |||
How to fix my spreadsheet from showing formula to showing answer | Excel Discussion (Misc queries) | |||
How to fix my spreadsheet from showing formula to showing answer | Excel Discussion (Misc queries) | |||
Date showing incorrect. 30:00 hrs showing 06:00 | Excel Discussion (Misc queries) | |||
Showing/ not showing "getting started" when excel starts | Excel Discussion (Misc queries) |