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
Highlighted
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
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies