Forum Discussion

BehroozRasuli's avatar
BehroozRasuli
Copper Contributor
Apr 30, 2021

Find Duplicates with Specific Codes

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, ...
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 30, 2021

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.

BehroozRasuli's avatar
BehroozRasuli
Copper Contributor
Apr 30, 2021

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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 30, 2021

    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

    Result is

    • BehroozRasuli's avatar
      BehroozRasuli
      Copper Contributor
      Apr 30, 2021
      Thank you very much, Sergei. It works 😉
      The best

Resources