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