SOLVED

How to Compare 2 Columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1939427%22%20slang%3D%22en-US%22%3EHow%20to%20Compare%202%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1939427%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EWould%20appreciate%20help%20on%20comparing%20column%20B%20to%20column%20A%26nbsp%3B%3C%2FP%3E%3CP%3Eof%20these%20email%20addresses.%3C%2FP%3E%3CP%3EThanks!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1939427%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1939444%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Compare%202%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1939444%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F885254%22%20target%3D%22_blank%22%3E%40Karen575%3C%2FA%3E%26nbsp%3BWhat%20would%20you%20like%20to%20compare%20and%20what%20outcome%20do%20you%20expect%3F%20At%20a%20glance%2C%20there%20are%20%26nbsp%3Bno%20matching%20email%20addresses%20between%20A%20and%20B%2C%20just%20duplicates%20in%20B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20real%20lists%20are%20much%20longer%20and%20you%20want%20to%20identify%20mail%20addresses%20in%20B%20that%20exist%20in%20A%2C%20you%20could%20use%20VLOOKUP%20(or%20XLOOKUP)%20if%20your%20Excel%20version%20supports%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1939465%22%20slang%3D%22de-DE%22%3ESubject%3A%20How%20to%20Compare%202%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1939465%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F885254%22%20target%3D%22_blank%22%3E%40Karen575%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECompare%20two%20columns%20quickly%3C%2FP%3E%3CP%3EIn%20this%20tip%2C%20we%20will%20show%20you%20how%20two%20columns%20can%20be%20compared%20quickly%20and%20easily%20with%20one%20another%20without%20much%20detour.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%20and%20column%20B%20serve%20as%20the%20starting%20point.%3C%2FP%3E%3CP%3EIn%20order%20to%20determine%20the%20cells%20in%20column%20B%20that%20differ%20from%20column%20A%2C%20simply%20proceed%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20cell%20range%20A2%3A%20B9%20(as%20an%20example)%3C%2FP%3E%3CP%3EUse%20the%20function%20key%20%5BF5%5D%20or%20the%20key%20combination%20%5BCtrl%5D%20%2B%20%5BG%5D%20to%20open%20the%20Go%20to%20dialog%20box%3C%2FP%3E%3CP%3EClick%20the%20Contents%20button%20to%20open%20the%20Select%20Contents%20dialog%20box%3C%2FP%3E%3CP%3EActivate%20the%20Cell%20differences%20option%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20now%20exit%20the%20dialog%20box%20by%20clicking%20the%20OK%20button%2C%20Excel%20will%20mark%20all%20cells%20in%20column%20B%20that%20differ%20from%20column%20A.%3C%2FP%3E%3CP%3EIf%2C%20for%20example%2C%20you%20change%20the%20font%20or%20background%20color%20of%20the%20selected%20lines%2C%20you%20will%20always%20see%20which%20cells%20do%20not%20match.%3C%2FP%3E%3CP%3EHowever%2C%20this%20function%20does%20not%20differentiate%20between%20upper%20and%20lower%20case.%20However%2C%20spaces%20are%20taken%20into%20account%20when%20determining%20the%20differences.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20even%20easier%20when%20there%20are%20numbers.%3C%2FP%3E%3CP%3EComparison%20between%20A%20and%20B%20column%20if%20there%20are%20numbers%2C%20einach%20%3D%20A1-B1%20in%20column%20C.%20If%20no%20zero%20then%20not%20equal.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20if%20you%20have%20done%20business%20at%20the%20end%20of%20my%20execution%2C%20here%20is%20a%20formula%20for%20your%20request.%3C%2FP%3E%3CP%3E%3DIF(A2%3DB2%3B%22%20OK%22%3B%22%20error%22)%3C%2FP%3E%3CP%3ESo%20attached%20is%20the%20file%20with%20the%20test.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope...%20I'm%20almost%20certain%20that%20this%20will%20work%20on%20your%20mac%20too%20...%20Even%20if%20I%20don't%20have%20one%20to%20try%20it%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

Would appreciate help on comparing column B to column A 

of these email addresses.

Thanks!!

4 Replies

@Karen575 What would you like to compare and what outcome do you expect? At a glance, there are  no matching email addresses between A and B, just duplicates in B.

 

If you real lists are much longer and you want to identify mail addresses in B that exist in A, you could use VLOOKUP (or XLOOKUP) if your Excel version supports it.

best response confirmed by Karen575 (New Contributor)
Solution

@Karen575 

Compare two columns quickly

In this tip, we will show you how two columns can be compared quickly and easily with one another without much detour.

 

Column A and column B serve as the starting point.

In order to determine the cells in column B that differ from column A, simply proceed as follows:

 

Select cell range A2: B9 (as an example)

Use the function key [F5] or the key combination [Ctrl] + [G] to open the Go to dialog box

Click the Contents button to open the Select Contents dialog box

Activate the Cell differences option there.

 

If you now exit the dialog box by clicking the OK button, Excel will mark all cells in column B that differ from column A.

If, for example, you change the font or background color of the selected lines, you will always see which cells do not match.

 However, this function does not differentiate between upper and lower case. However, spaces are taken into account when determining the differences.

 

It's even easier when there are numbers.

Comparison between A and B column if there are numbers, einach = A1-B1 in column C. If no zero then not equal.

 

But if you have done business at the end of my execution, here is a formula for your request.

=IF(A2=B2;"OK";"error")

Also attached is the file with the test.

 

Hope ... I'm almost certain that this will work on your mac too ... even if I don't have one to try it out.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

Worked like a charm, thx, Nikolino!!
You are welcome
I was pleased to be able to help you.

Nikolino
I know I don't know anything (Socrates)