Home

textjoin issues

%3CLINGO-SUB%20id%3D%22lingo-sub-665443%22%20slang%3D%22en-US%22%3Etextjoin%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-665443%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20list%20the%20unique%20values%20for%20each%20row%20as%20an%20output%2C%20separated%20by%20a%20comma%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConsider%20cells%20A2%3Db%20B2%3Db%20C2%3Dd%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%20the%20below%20formula%20and%20get%20cell%20D2%20b%2Cd%3C%2FP%3E%3CP%3EDuplicates%20are%20removed%20as%20well%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DTEXTJOIN(%22%2C%22%2CTRUE%2CIF(A2%3AC2%26lt%3B%26gt%3B%22%22%2CIF(COLUMN(A2%3AC2)%3DMATCH(A2%3AC2%2CA2%3AC2%2C0)%2CA2%3AC2%2C%22%22)%2C%22%22))%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EBut%20if%20I%20start%20from%20B2%3Db%20C2%3Db%20and%20D2%3Dd%20and%20use%20the%20formula%20in%20E2%2C%20it%20doesnt%20work%2C%20I%20am%20using%20Column%20A%20for%20some%20other%20purpose%3C%2FP%3E%3CP%3EBased%20on%20the%20information%20shared%20above%2C%20I%20want%20my%20formula%20to%20work%20fine%2C%20when%20I%20insert%20a%20column%20in%20column%20A%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-665443%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-665577%22%20slang%3D%22en-US%22%3ERe%3A%20textjoin%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-665577%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F353876%22%20target%3D%22_blank%22%3E%40vishu1204%3C%2FA%3E%26nbsp%3B%2C%20that's%20as%3C%2FP%3E%0A%3CPRE%3E%3DTEXTJOIN(%22%2C%22%2CTRUE%2CIF(B2%3AD2%26lt%3B%26gt%3B%22%22%2CIF(COLUMN(B2%3AD2)%2B1-COLUMN(B2)%3DMATCH(B2%3AD2%2CB2%3AD2%2C0)%2CB2%3AD2%2C%22%22)%2C%22%22))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
vishu1204
Occasional Visitor

I would like to list the unique values for each row as an output, separated by a comma

 

Example

 

Consider cells A2=b B2=b C2=d

 

I use the below formula and get cell D2 b,d

Duplicates are removed as well

 

=TEXTJOIN(",",TRUE,IF(A2:C2<>"",IF(COLUMN(A2:C2)=MATCH(A2:C2,A2:C2,0),A2:C2,""),""))


But if I start from B2=b C2=b and D2=d and use the formula in E2, it doesnt work, I am using Column A for some other purpose

Based on the information shared above, I want my formula to work fine, when I insert a column in column A

1 Reply

@vishu1204 , that's as

=TEXTJOIN(",",TRUE,IF(B2:D2<>"",IF(COLUMN(B2:D2)+1-COLUMN(B2)=MATCH(B2:D2,B2:D2,0),B2:D2,""),""))

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies