Find Duplicates with Specific Codes

New Contributor

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


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?


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


Result is


Thank you very much, Sergei. It works
The best

@BehroozRasuli , you are welcome