Forum Discussion

SunilD2007's avatar
SunilD2007
Copper Contributor
May 31, 2024

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

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 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. 

  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor

    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's avatar
      SunilD2007
      Copper Contributor
      Thanks SnowMan55! This worked perfectly! Really appreciate the fast response and the accurate solution!
  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor

    SunilD2007 Try this single cell dynamic spill array formula-

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

     

    • SunilD2007's avatar
      SunilD2007
      Copper Contributor
      Thank you, Harun24HR. I actually used the suggestion from SnowMan55 and that worked perfectly. Thank you for taking the time to respond!

Resources