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, 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.

Formulas and Functions

5 Replies

  • 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

Resources