Find Duplicates with Specific Codes

%3CLINGO-SUB%20id%3D%22lingo-sub-2311597%22%20slang%3D%22en-US%22%3EFind%20Duplicates%20with%20Specific%20Codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2311597%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3CBR%20%2F%3EI%20have%20a%20list%20of%20terms%20(also%20duplicates)%20in%20column%20A%20and%20specific%20codes%20for%20each%20term%20in%20column%20B.%20Now%2C%20I%20want%20to%20make%20a%20list%20contains%20unique%20terms%20and%20those%20codes%20which%20belong%20to%20the%20terms.%20For%20example%2C%20if%20%22book%22%20repeats%20in%20two%20rows%2C%20with%20codes%2032%20and%2065%3B%20I%20want%20to%20make%20a%20list%20with%20just%20one%20%22book%22%20in%20cell%20A1%20and%2032%2C%2065%20in%20cell%20B1.%20Can%20you%20help%20me%2C%20please%3F%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2311597%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2311676%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20Duplicates%20with%20Specific%20Codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2311676%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042009%22%20target%3D%22_blank%22%3E%40BehroozRasuli%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTEXTJOIN(%22%2C%20%22%2CTRUE%2C%20IF(A%3AA%3DE2%2CB%3AB%2C%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EDepends%20on%20which%20Excel%20you%20are%20it%20could%20be%20entered%20as%20regular%20formula%20or%20with%20Ctrl%2BShift%2BEnter.%20Please%20check%20in%20attached.%3C%2FP%3E%0A%3CP%3EAnd%20from%20performance%20point%20of%20view%20it's%20better%20to%20use%20reference%20not%20on%20entire%20columns%20but%20on%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2311709%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20Duplicates%20with%20Specific%20Codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2311709%22%20slang%3D%22en-US%22%3EDear%20Sergei%20Baklan%2C%3CBR%20%2F%3EThank%20you%20so%20much%20for%20your%20help.%20I%20think%20TEXTJOIN%20does%20not%20work%20in%20MS%20Excel%202010%2C%20unfortunately!%20I%20there%20any%20other%20solutions%3F%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E
New Contributor

Hello,
I have a list of terms (also duplicates) in column A and specific codes for each term in column B. Now, I want to make a list contains unique terms and those codes which belong to the terms, using a formula. For example, if "book" repeats in two rows, with codes 32 and 65; I want to make a list with just one "book" in cell A1 and 32, 65 in cell B1. Can you help me, please? Thanks.

5 Replies

@BehroozRasuli 

That could be

=TEXTJOIN(", ",TRUE, IF(A:A=E2,B:B,""))

Depends on which Excel you are it could be entered as regular formula or with Ctrl+Shift+Enter. Please check in attached.

And from performance point of view it's better to use reference not on entire columns but on range.

Dear Sergei Baklan,
Thank you so much for your help. I think TEXTJOIN does not work in MS Excel 2010, unfortunately! Are there any other solutions?
Thanks

@BehroozRasuli 

Oops... I guess 2010 works with data model, you may create PivotTable on your range adding the measure as

=IF(ISFILTERED(Range[Terms]), CONCATENATEX(Range, Range[Codes], ", "), "")

(right click on range and add measure

image.png

Result is

image.png

Thank you very much, Sergei. It works
The best

@BehroozRasuli , you are welcome