Forum Discussion

Kirsty Lowe's avatar
Kirsty Lowe
Copper Contributor
May 01, 2018
Solved

Removing duplicates when using TEXTJOIN

Hi, I am using TEXTJOIN to being into one cell entries from a separate column, where I am ignoring empty cells and separately by &. This works fine. However, I can have duplicate values in an indi...
Formulas & Functions
TEXTJOIN
  • Haytham Amairah's avatar
    Haytham Amairah
    May 01, 2018

    Kristy,

     

    I got the below formula for you thanks to this amazing https://www.youtube.com/watch?v=QJ2O07EB80Q!

    =TEXTJOIN(" & ",TRUE,IF(MATCH(A1:A6,A1:A6,0)=ROW(A1:A6)-ROW(A1)+1,A1:A6,""))

    Please find it in the attached file.

     

    Regards

leo_bourikov's avatar
leo_bourikov
Copper Contributor
Sep 02, 2020

Kirsty Lowe I did this and attached screenshot (Excel 2016):

=TEXTJOIN(",",TRUE,UNIQUE(IF(A:A=A2,B:B,"")))

 

Hope it helps!

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 02, 2020

    leo_bourikov 

    As variant

    =ARRAYTOTEXT(UNIQUE(FILTER(B:B,A:A=A2,"")))

     

    • Mikael_Toivonen's avatar
      Mikael_Toivonen
      Copper Contributor
      Jan 16, 2022

      This is a great answer if using a one the the more modern Excel versions. FILTER(), UNIQUE() and ARRAYTOTEXT() used in a clear, no-nonsense way.

    • leo_bourikov's avatar
      leo_bourikov
      Copper Contributor
      Sep 03, 2020

      SergeiBaklan thanks for sharing, unfortunately I cannot use this one as my Excel 2016 must be older version that does not contain ARRAYTOTEXT 😞

Resources