May 01 2018
05:40 AM
- last edited on
Jul 25 2018
11:53 AM
by
TechCommunityAP
May 01 2018
05:40 AM
- last edited on
Jul 25 2018
11:53 AM
by
TechCommunityAP
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:
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
May 01 2018 07:09 AM - edited May 01 2018 07:10 AM
SolutionKristy,
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
May 01 2018 07:22 AM
May 01 2018 07:23 AM
Hi Haytham,
Thank you for your response.
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
May 01 2018 07:24 AM
Thank you both.
I'll check the video out.
May 01 2018 07:33 AM
May 01 2018 07:40 AM
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!
Sep 01 2020 06:41 PM
@Kirsty Lowe I did this and attached screenshot (Excel 2016):
=TEXTJOIN(",",TRUE,UNIQUE(IF(A:A=A2,B:B,"")))
Hope it helps!
Sep 02 2020 01:10 PM
Sep 03 2020 05:28 AM
@Sergei Baklan thanks for sharing, unfortunately I cannot use this one as my Excel 2016 must be older version that does not contain ARRAYTOTEXT
Oct 18 2021 04:55 PM
Nov 22 2021 06:52 AM
Jan 16 2022 05:00 AM - edited Jan 16 2022 05:01 AM
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.
Nov 04 2022 05:08 AM
THIS!!! Simple, effective, winning!
Jan 05 2023 05:28 AM
To improve performance using entire column I'd modify as
=TEXTJOIN(" & ",,UNIQUE( TOCOL( F:F,3)) )