Compare two tables & Highlight difference

%3CLINGO-SUB%20id%3D%22lingo-sub-2220704%22%20slang%3D%22en-US%22%3ECompare%20two%20tables%20%26amp%3B%20Highlight%20difference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2220704%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Experts%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20be%20helpful%20if%20spare%20some%20time%20%26amp%3B%20help%20me%20with%20below%20task%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETask%3A%20To%20highlight%20differences%20(cells)%20in%20attached%20excel%20by%20comparing%20two%20tables.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBackground%20%3A%20We%20have%20two%20tables%20(%20Toy_Table%20%26amp%3B%20Toy_Drawer)%20in%20same%20sheet%20(%20sheet1).%20please%20suggest%20fastest%20way%20to%20compare%20both%20tables%20%26amp%3B%20highlight%20differences%20in%20both%20tables.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdditional%20information%20%3A%3C%2FP%3E%3CUL%3E%3CLI%3EWindows%2010%3C%2FLI%3E%3CLI%3EMS%20excel%202016%20(%20desktop%20version)%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3ERajiv%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2220704%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2220874%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20two%20tables%20%26amp%3B%20Highlight%20difference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2220874%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1001822%22%20target%3D%22_blank%22%3E%40Rajiv_Kokitkar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20use%20conditional%20formatting%20with%20below%20formula%20to%20highlight%20cells%20that%20do%20not%20match%20within%20two%20tables%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFNA(MATCH(G2%2C%24C%242%3A%24C%248%2C0)%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22tauqeeracma_0-1616094592703.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F265344i95771AC28AB9618F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22tauqeeracma_0-1616094592703.png%22%20alt%3D%22tauqeeracma_0-1616094592703.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20sample%20file%20is%20also%20attached%20for%20your%20reference.%20Please%20let%20me%20know%20if%20it%20works%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello Experts,

 

Would be helpful if spare some time & help me with below task ?

 

Task: To highlight differences (cells) in attached excel by comparing two tables.

 

Background : We have two tables ( Toy_Table & Toy_Drawer) in same sheet ( sheet1). please suggest fastest way to compare both tables & highlight differences in both tables.

 

Additional information :

  • Windows 10
  • MS excel 2016 ( desktop version)

Thanks,

Rajiv

3 Replies

Hi @Rajiv_Kokitkar 

You may use conditional formatting with below formula to highlight cells that do not match within two tables:

=IFNA(MATCH(G2,$C$2:$C$8,0),0)

 

tauqeeracma_0-1616094592703.png

 

A sample file is also attached for your reference. Please let me know if it works for you.

 

Thanks

Tauqeer

Thank you Tauqeer . This is helpful. Is it possible to highlight cells from column
'key' and 'Toy' which are not matching? 'Drawer' will be reference column in both tables.

If not, I can still live with this solution

Hi @Rajiv_Kokitkar 

 

Yes, you can highlight the entire row instead of cells. I have modified below formula in attached file.

 

tauqeeracma_1-1616144667531.png

 

Hope it works as desired now.

 

Thanks,

Tauqeer