Need to select unique cells from 2 worksheets with identical column names and highlight differences

%3CLINGO-SUB%20id%3D%22lingo-sub-1842670%22%20slang%3D%22en-US%22%3ENeed%20to%20select%20unique%20cells%20from%202%20worksheets%20with%20identical%20column%20names%20and%20highlight%20differences%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1842670%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3A%3C%2FP%3E%3CP%3EI%20have%202%20identical%20worksheets%20that%20will%20be%20used%20as%20a%20data%20entry%20worksheet%20and%20the%20second%20will%20also%20be%20used%20as%20a%20data%20entry%20worksheet%20by%20a%20second%20person%20entering%20the%20same%20data%20as%20a%20double%20check%20for%20the%20first%20person%20who%20entered%20the%20same%20exact%20data.%20The%20process%20is%20as%20follows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20First%20person%20enters%20incoming%20customer%20information%20into%20Excel%20spreadsheet.%3C%2FP%3E%3CP%3E2)%20Second%20person%20enters%20the%20same%20exact%20information%20into%20a%20different%20worksheet%20to%20compare%20the%20accuracy%20of%20the%20first%20data%20entry%20person.%26nbsp%3B%3C%2FP%3E%3CP%3E3)%20Differences%20in%20cells%20should%20be%20highlighted%20using%20conditional%20formatting%20as%20red%20to%20indicate%20a%20difference%20in%20between%20the%20data%20in%20each%20cell%20of%20sheet%201%20and%20sheet%202%20.%3C%2FP%3E%3CP%3E4)%20Example%3A%20Name%20field%20typed%20in%20by%20First%20person%20into%20first%20spreadsheet.%26nbsp%3B%20Full%20name%20field%20first%20person%20type%20in%20the%20following%20into%20name%20field%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BJoh%20Doe%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20Second%20person%20types%20in%20John%20Doe%20correctly%3C%2FP%3E%3CP%3E5)%20Process%20should%20flag%20the%20Name%20Field%20as%20red%20because%20difference%20in%20Name%20field.%3C%2FP%3E%3CP%3E6)%20Also%20if%202nd%20person%20makes%20the%20mistake%20reverse%20of%20above%20field%20should%20turn%20yellow.%20Instead%20of%20Red.%3C%2FP%3E%3CP%3E7)%20So%20if%20first%20person%20enters%20any%20field%20data%20incorrectly%20it%20gets%20flagged%20as%20red%20and%20unique%20because%20Jon%20does%20not%20equal%20Jon.%20If%202nd%20person%20enters%20Jon%20instead%20of%20John%20it%20gets%20flagged%20as%20yellow.%20This%20way%20we%20know%20which%20person%20had%20the%20incorrect%20entry.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20how%20to%20do%20this%20using%20cells%20in%20the%20same%20sheet%20but%20I%20haven't%20done%20this%20using%20a%20comparison%20between%20two%20sheets%20in%20the%20same%20workbook.%20Also%20just%20the%20different%20cells%20get%20highlighted%20through%20the%20conditional%20formatting%20not%20the%20whole%20row.%20So%20you%20can%20have%20a%20situation%20where%20Name%20Field%20is%20highlighted%26nbsp%3B%20red%20or%20yellow%20and%20Address%20field%20is%20highlighted%20red%20or%20yellow.%20Please%20advice%20and%20thank%20you%20for%20your%20time.%20Also%20these%20two%20spreadsheets%20will%20be%20data%20entry%20so%20many%20records%20will%20be%20added%20daily%20and%20this%20formatting%20should%20still%20work%20without%20re-doing%20it%20everyday%20or%20time%20more%20data%20is%20added%20to%20the%20spreadsheet.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1842670%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1843369%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20select%20unique%20cells%20from%202%20worksheets%20with%20identical%20column%20names%20and%20highlight%20differen%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1843369%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F853700%22%20target%3D%22_blank%22%3E%40RickZler%3C%2FA%3E%26nbsp%3BA%20CF-rule%20in%20Sheet1%20like%20%3CSTRONG%3E%3DA1%26lt%3B%26gt%3BSheet2!A1%20%3C%2FSTRONG%3Eapplied%20to%20the%20entire%20data%20range%2C%20for%20example%20%3CSTRONG%3E%24A%241%3A%24B%242500%3C%2FSTRONG%3E%26nbsp%3B%26nbsp%3Bshould%20allow%20you%20to%20highlight%20differences%20between%20the%20two%20sheets%20for%20every%20single%20cell%20in%20the%20applied%20range.%20However%2C%20Excel%20can%20not%20tell%20which%20entries%20are%20correct%20and%20which%20are%20not.%20Therefore%2C%20highlighting%20incorrect%20entries%20by%20the%20second%20person%20makes%20no%20sense%2C%20as%20the%20same%20cells%20will%20show%20up%20as%20%22not%20equal%22.%26nbsp%3BYou'll%20need%20a%20third%20person%20to%20judge%20who%20entered%20the%20data%20correctly.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi:

I have 2 identical worksheets that will be used as a data entry worksheet and the second will also be used as a data entry worksheet by a second person entering the same data as a double check for the first person who entered the same exact data. The process is as follows.

 

1) First person enters incoming customer information into Excel spreadsheet.

2) Second person enters the same exact information into a different worksheet to compare the accuracy of the first data entry person. 

3) Differences in cells should be highlighted using conditional formatting as red to indicate a difference in between the data in each cell of sheet 1 and sheet 2 .

4) Example: Name field typed in by First person into first spreadsheet.  Full name field first person type in the following into name field

     Joh Doe

    Second person types in John Doe correctly

5) Process should flag the Name Field as red because difference in Name field.

6) Also if 2nd person makes the mistake reverse of above field should turn yellow. Instead of Red.

7) So if first person enters any field data incorrectly it gets flagged as red and unique because Jon does not equal Jon. If 2nd person enters Jon instead of John it gets flagged as yellow. This way we know which person had the incorrect entry.

 

I know how to do this using cells in the same sheet but I haven't done this using a comparison between two sheets in the same workbook. Also just the different cells get highlighted through the conditional formatting not the whole row. So you can have a situation where Name Field is highlighted  red or yellow and Address field is highlighted red or yellow. Please advice and thank you for your time. Also these two spreadsheets will be data entry so many records will be added daily and this formatting should still work without re-doing it everyday or time more data is added to the spreadsheet.

Thanks

 

 

2 Replies
Highlighted

@RickZler A CF-rule in Sheet1 like =A1<>Sheet2!A1 applied to the entire data range, for example $A$1:$M$2500 , should allow you to highlight differences between the two sheets for every single cell in the applied range. However, Excel can not tell which entries are correct and which are not. Therefore, highlighting incorrect entries by the second person makes no sense, as the same cells will show up as "not equal". You'll need a third person to judge who entered the data correctly.

Highlighted

@Riny_van_Eekelen 

 

Hi:

 

Thanks for the reply and understand the error showing on compliancy rules. I'll try this solution but I seem to recall this will not propagate to newly entered records unless I add a bunch of blank records to each of the comparison spreadsheets and apply the formatting to those blanks. Is this correct?  So it wont actually apply to new entries into the spreadsheet only roes that already have it applied in the formatting rules for those cells ? 

Thanks