SOLVED

# Removing duplicates when using TEXTJOIN

Occasional Contributor

# 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 individual cell in the column I am using in my TEXTJOIN and I'd like to be able to supress duplicates.

For example:

Column F on Worksheet 1 has the following in the 6 cells:

• 123, 890, 22
• 560
• 123, 890, 22
• 22
• test
• test

Ideally what I would like in my single cell is:

123, 890, 22 & 560 & test

But I realise that is probably unrealistic.

So I'd like to be able to get to (e.g. hiding the cell that exactly matches the other):

123, 890, 22 & 560 & 22 & test

The function I am using is:

=TEXTJOIN(" & ",TRUE,'1'!\$F\$10:\$F\$606)

This returns:

123, 890, 22 & 560 & 123, 890, 22 & 22 & test & test

I have looked into combining this function with 'MATCH' but it hasn't worked, I get value errors.  I assume maybe because I have a mix of numerics and characters?

Many thanks

Kirsty

14 Replies
best response confirmed by Kirsty Lowe (Occasional Contributor)
Solution

# Re: Removing duplicates when using TEXTJOIN

Kristy,

I got the below formula for you thanks to this amazing video!

`=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

# Re: Removing duplicates when using TEXTJOIN

@Haytham Amairah

I was thinking of the same video. :-)

# Re: Removing duplicates when using TEXTJOIN

Hi Haytham,

However, I get a VALUE error in Excel:

=TEXTJOIN(" & ",TRUE,IF(MATCH('1'!\$F\$10:\$F\$606,0)=ROW('1'!\$F\$10:\$F\$606)-ROW('1'!\$F\$10)+1,'1'!\$F\$10:\$F\$606,""))

Is what I translated your formula in to.

Kirsty

# Re: Removing duplicates when using TEXTJOIN

Thank you both.

I'll check the video out.

# Re: Removing duplicates when using TEXTJOIN

Hi,
I have also tried by watching the video and placing the formula into my sheet so I didn't have to reference a different worksheet, and it still fails to evaluate the data.
=TEXTJOIN(", ",,IF(MATCH(F10:F50,F10:F50,0)=(ROW(F10:F50)-ROW(F9)),F10:F50,""))
Sorry!
Kirsty

# Re: Removing duplicates when using TEXTJOIN

So sorry I got there in the end, with help from @Haytham Amairah

I went back to the video you linked and used the following formula, remembering to use Ctrl+Shift+Enter to apply it, and this time it worked :)

{=TEXTJOIN(", ",,IF(F10:F50<>"",IF(MATCH(F10:F50,F10:F50,0)=(ROW(F10:F50)-ROW(F9)),F10:F50,""),""))}

Thank you!

# Re: Removing duplicates when using TEXTJOIN

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

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

Hope it helps!

# Re: Removing duplicates when using TEXTJOIN

As variant

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

# Re: Removing duplicates when using TEXTJOIN

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

# Re: Removing duplicates when using TEXTJOIN

@leo_bourikov Thanks it helped

# Re: Removing duplicates when using TEXTJOIN

Just want to say thanks. This post helped me today with the same issue!

# Re: Removing duplicates when using TEXTJOIN

you just might want to use this formula: =TEXTJOIN(" & ",TRUE,UNIQUE(F:F,FALSE,FALSE))

# Re: Removing duplicates when using TEXTJOIN

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.

# Re: Removing duplicates when using TEXTJOIN

THIS!!! Simple, effective, winning!