SOLVED

compare two columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1396545%22%20slang%3D%22en-US%22%3Ecompare%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1396545%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%3C%2FP%3E%3CP%3Ei%20am%20new%20to%20excel%2C%20i%20have%20two%20columns%2C%20in%20column%20A%20i%20have%20500%20records%2C%20in%20Column%20B%20i%20have%20200%20records.%20i%20want%20to%20compare%20Column%20B%20with%20Column%20A%20and%20pull%20the%20matching%20records%20in%20Column%20C.%20When%20i%20go%20to%26nbsp%3B%3C%2FP%3E%3CP%3EConditional%20Formatting--%26gt%3BFormat%20only%20unique%20or%20duplicate%20value%2C%20but%20i%20dont%20see%20option%20to%20pull%20the%20matching%20data%20to%20column%20C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1396545%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%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-1396551%22%20slang%3D%22en-US%22%3ERe%3A%20compare%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1396551%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F139420%22%20target%3D%22_blank%22%3E%40Rising%20Flight%3C%2FA%3E%3CSPAN%3E%26nbsp%3BThis%20could%20be%2C%20e.g.%20in%20C1%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(VLOOKUP(B1%2CA%3AA%2C2%2CFALSE)%2C%22Not%20Found%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20copy%20it%20dow%20column%20B.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1396575%22%20slang%3D%22en-US%22%3ERe%3A%20compare%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1396575%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Riny%3C%2FP%3E%3CP%3EIn%20the%20below%20screenshot%20i%20have%20ColumnA%20and%20ColumnB%20and%20in%20Column%20C%20i%20only%20want%20to%20get%20duplicate%20records%2C%20so%20the%20output%20i%20want%20to%20get%20in%20Column%20C%20is%20SrvC%2CSrvB%2CSrvA%2CSrvF%20since%20these%204%20records%20in%20Column%20B%20are%20matching%20with%20Column%20A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22col.JPG%22%20style%3D%22width%3A%20220px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F192344i32E9C93527EFE338%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22col.JPG%22%20alt%3D%22col.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1396652%22%20slang%3D%22en-US%22%3ERe%3A%20compare%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1396652%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F139420%22%20target%3D%22_blank%22%3E%40Rising%20Flight%3C%2FA%3E%26nbsp%3BSee%20now%20that%20I%20made%20a%20mistake%20in%20the%20formula.%20Sorry!%20It%20should%20have%20been%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(VLOOKUP(B1%2CA%3AA%2C1%2CFALSE)%2C%22Not%20Found%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%2C%20since%20you%20are%20not%20interested%20to%20see%20the%20%22Not%20Found%22%2C%20you%20could%20consider%20to%20filter%20these%20out.%20A%20manual%20process%20you%20probable%20don't%20want%20either.%3C%2FP%3E%3CP%3EThis%20made%20me%20re-think%20and%20I%20could%20come%20up%20with%20the%20following%20(not%20most%20elegant)%20solution%3A%3C%2FP%3E%3CP%3E%26nbsp%3BThis%20formula%20works%20only%20if%20your%20Excel%20version%20supports%20the%20relatively%20new%20FILTER%20function.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(VLOOKUP(B1%3AB5%2CA%3AA%2C1%2CFALSE)%2CNOT(ISNA(VLOOKUP(B1%3AB5%2CA%3AA%2C1%2CFALSE))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20demonstrated%20in%20the%20attached%20workbook.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Contributor

Hi All

i am new to excel, i have two columns, in column A i have 500 records, in Column B i have 200 records. i want to compare Column B with Column A and pull the matching records in Column C. When i go to 

Conditional Formatting-->Format only unique or duplicate value, but i dont see option to pull the matching data to column C 

 

3 Replies
Highlighted

@Rising Flight This could be, e.g. in C1

 

=IFERROR(VLOOKUP(B1,A:A,2,FALSE),"Not Found")

 

and copy it dow column B.

Highlighted

Thanks Riny

In the below screenshot i have ColumnA and ColumnB and in Column C i only want to get duplicate records, so the output i want to get in Column C is SrvC,SrvB,SrvA,SrvF since these 4 records in Column B are matching with Column A

col.JPG

Highlighted
Best Response confirmed by Rising Flight (Frequent Contributor)
Solution

@Rising Flight See now that I made a mistake in the formula. Sorry! It should have been:

 

=IFERROR(VLOOKUP(B1,A:A,1,FALSE),"Not Found")

 

But, since you are not interested to see the "Not Found", you could consider to filter these out. A manual process you probable don't want either.

This made me re-think and I could come up with the following (not most elegant) solution:

 This formula works only if your Excel version supports the relatively new FILTER function. 

 

=FILTER(VLOOKUP(B1:B5,A:A,1,FALSE),NOT(ISNA(VLOOKUP(B1:B5,A:A,1,FALSE))))

 

It's demonstrated in the attached workbook.