SOLVED
Home

Removing duplicates when using TEXTJOIN

%3CLINGO-SUB%20id%3D%22lingo-sub-188950%22%20slang%3D%22en-US%22%3ERemoving%20duplicates%20when%20using%20TEXTJOIN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-188950%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20using%20TEXTJOIN%20to%20being%20into%20one%20cell%20entries%20from%20a%20separate%20column%2C%20where%20I%20am%20ignoring%20empty%20cells%20and%20separately%20by%20%26amp%3B.%3C%2FP%3E%3CP%3EThis%20works%20fine.%3C%2FP%3E%3CP%3EHowever%2C%20I%20can%20have%20duplicate%20values%20in%20an%20individual%20cell%20in%20the%20column%20I%20am%20using%20in%20my%20TEXTJOIN%20and%20I'd%20like%20to%20be%20able%20to%20supress%20duplicates.%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20F%20on%20Worksheet%201%20has%20the%20following%20in%20the%26nbsp%3B6%20cells%3A%3C%2FP%3E%3CUL%3E%3CLI%3E123%2C%20890%2C%2022%3C%2FLI%3E%3CLI%3E560%3C%2FLI%3E%3CLI%3E123%2C%20890%2C%2022%3C%2FLI%3E%3CLI%3E22%3C%2FLI%3E%3CLI%3Etest%3C%2FLI%3E%3CLI%3Etest%3C%2FLI%3E%3C%2FUL%3E%3CP%3EIdeally%20what%20I%20would%20like%20in%20my%20single%20cell%20is%3A%3C%2FP%3E%3CP%3E123%2C%20890%2C%2022%20%26amp%3B%26nbsp%3B560%20%26amp%3B%26nbsp%3Btest%3C%2FP%3E%3CP%3EBut%20I%20realise%20that%20is%20probably%20unrealistic.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I'd%20like%20to%20be%20able%20to%20get%20to%20(e.g.%20hiding%20the%20cell%20that%20exactly%20matches%20the%20other)%3A%3C%2FP%3E%3CP%3E123%2C%20890%2C%2022%20%26amp%3B%26nbsp%3B560%20%26amp%3B%26nbsp%3B22%20%26amp%3B%26nbsp%3Btest%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20function%20I%20am%20using%20is%3A%3C%2FP%3E%3CP%3E%3DTEXTJOIN(%22%20%26amp%3B%20%22%2CTRUE%2C'1'!%24F%2410%3A%24F%24606)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20returns%3A%3C%2FP%3E%3CP%3E123%2C%20890%2C%2022%20%26amp%3B%20560%20%26amp%3B%20123%2C%20890%2C%2022%20%26amp%3B%2022%20%26amp%3B%20test%20%26amp%3B%20test%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20looked%20into%20combining%20this%20function%20with%20'MATCH'%20but%20it%20hasn't%20worked%2C%20I%20get%20value%20errors.%26nbsp%3B%20I%20assume%20maybe%20because%20I%20have%20a%20mix%20of%20numerics%20and%20characters%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%3C%2FP%3E%3CP%3EKirsty%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-188950%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETEXTJOIN%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-189012%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20duplicates%20when%20using%20TEXTJOIN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-189012%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20sorry%20I%20got%20there%20in%20the%20end%2C%20with%20help%20from%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20went%20back%20to%20the%20video%20you%20linked%20and%20used%20the%20following%20formula%2C%20remembering%20to%20use%20Ctrl%2BShift%2BEnter%20to%20apply%20it%2C%20and%20this%20time%20it%20worked%20%3A)%3C%2FP%3E%3CP%3E%7B%3DTEXTJOIN(%22%2C%20%22%2C%2CIF(F10%3AF50%26lt%3B%26gt%3B%22%22%2CIF(MATCH(F10%3AF50%2CF10%3AF50%2C0)%3D(ROW(F10%3AF50)-ROW(F9))%2CF10%3AF50%2C%22%22)%2C%22%22))%7D%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-189005%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20duplicates%20when%20using%20TEXTJOIN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-189005%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3EI%20have%20also%20tried%20by%20watching%20the%20video%20and%20placing%20the%20formula%20into%20my%20sheet%20so%20I%20didn't%20have%20to%20reference%20a%20different%20worksheet%2C%20and%20it%20still%20fails%20to%20evaluate%20the%20data.%3CBR%20%2F%3E%3DTEXTJOIN(%22%2C%20%22%2C%2CIF(MATCH(F10%3AF50%2CF10%3AF50%2C0)%3D(ROW(F10%3AF50)-ROW(F9))%2CF10%3AF50%2C%22%22))%3CBR%20%2F%3ESorry!%3CBR%20%2F%3EKirsty%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-188995%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20duplicates%20when%20using%20TEXTJOIN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-188995%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20both.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'll%20check%20the%20video%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-188994%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20duplicates%20when%20using%20TEXTJOIN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-188994%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Haytham%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20response.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20get%20a%20VALUE%20error%20in%20Excel%3A%3C%2FP%3E%3CP%3E%3DTEXTJOIN(%22%20%26amp%3B%20%22%2CTRUE%2CIF(MATCH('1'!%24F%2410%3A%24F%24606%2C0)%3DROW('1'!%24F%2410%3A%24F%24606)-ROW('1'!%24F%2410)%2B1%2C'1'!%24F%2410%3A%24F%24606%2C%22%22))%3C%2FP%3E%3CP%3EIs%20what%20I%20translated%20your%20formula%20in%20to.%3C%2FP%3E%3CP%3EKirsty%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-188993%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20duplicates%20when%20using%20TEXTJOIN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-188993%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20was%20thinking%20of%20the%20same%20video.%20%3A-)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-188986%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20duplicates%20when%20using%20TEXTJOIN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-188986%22%20slang%3D%22en-US%22%3E%3CP%3EKristy%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20got%20the%20below%20formula%20for%20you%20thanks%20to%20this%20amazing%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DQJ2O07EB80Q%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Evideo%3C%2FA%3E!%3C%2FP%3E%3CPRE%3E%3DTEXTJOIN(%22%20%26amp%3B%20%22%2CTRUE%2CIF(MATCH(A1%3AA6%2CA1%3AA6%2C0)%3DROW(A1%3AA6)-ROW(A1)%2B1%2CA1%3AA6%2C%22%22))%3C%2FPRE%3E%3CP%3EPlease%20find%20it%20in%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E
Kirsty Lowe
Occasional Contributor

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

 

6 Replies
Solution

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

@Haytham Amairah

I was thinking of the same video. :-)

 

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

Thank you both.

 

I'll check the video out.

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

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!

 
 
 
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies