SOLVED

How to normalize comma separated values that are the same but were loaded in a different order

Copper Contributor

Hello, looking for some help.  Is there an easy way to normalize the following table?  The values in column B are all the same, they were just loaded in a different order.  Is there a way to mass update so that these appear in the same order.  The file itself contains many combinations...for example, there may be cells that have 3 comma separated values but each value is the same, just in a different load order.  Or 4 values, in a different load order.  Thank you in advance!!

SunilD2007_0-1717198561066.png

 

4 Replies
best response confirmed by SunilD2007 (Copper Contributor)
Solution

@SunilD2007 Yes, an easy solution is available if you are using Excel 365 or Excel for the web (as the TEXTSPLIT function is used).  Assuming that each word/term is separated by a comma and a space:

=TEXTJOIN(", ", TRUE, SORT( TRANSPOSE( TEXTSPLIT(B2, ", ") ) ) )

(Except for the space in the literals, spaces are for increased readability, and are not required.)

 

See the attached workbook. 

@SunilD2007 Try this single cell dynamic spill array formula-

=MAP(B2:INDEX(B2:B50000,COUNTA(B2:B50000)),
LAMBDA(x,TEXTJOIN(", ", 1, SORT(TEXTSPLIT(x, ,", ") ))))

 

Thanks @SnowMan55! This worked perfectly! Really appreciate the fast response and the accurate solution!
Thank you, @Harun24HR. I actually used the suggestion from SnowMan55 and that worked perfectly. Thank you for taking the time to respond!
1 best response

Accepted Solutions
best response confirmed by SunilD2007 (Copper Contributor)
Solution

@SunilD2007 Yes, an easy solution is available if you are using Excel 365 or Excel for the web (as the TEXTSPLIT function is used).  Assuming that each word/term is separated by a comma and a space:

=TEXTJOIN(", ", TRUE, SORT( TRANSPOSE( TEXTSPLIT(B2, ", ") ) ) )

(Except for the space in the literals, spaces are for increased readability, and are not required.)

 

See the attached workbook. 

View solution in original post