Forum Discussion

ECD333285's avatar
ECD333285
Copper Contributor
Jul 04, 2024
Solved

Sorting on multiples in same column

Hi there - Cannot figure this out. I have a list of items in a column as follows:

5" White

5" Red

5" Blue

11" White

11" Red

11" Blue

17" White

17" Red

17" Blue

There are several more sizes but I need to sort this sheet so that the colors fall in order and the sizes order as 11, 17, 5. 

 

11" White

17" White

5" White

11" Red

17" Red

5" Red

And so on. The colors are in a specific order not based on alphabet.

Thank you in advance!

 

excel
Formulas and Functions
  • djclements's avatar
    djclements
    Jul 10, 2024

    ECD333285 No worries. The original formula sorted the data alphanumerically, treating the sizes as text (anything that begins with a "1" appears first, then "2" and so on. To sort them in a specified order, you can create an array of sizes in the order of your choosing, then use the XMATCH method again to generate the size_ID. For example, the formula for Excel 2021 could be modified as follows:

     

    =LET(
    data, FILTER(A2:A1000, A2:A1000<>""),
    num, SEARCH(" ", data),
    color, RIGHT(data, LEN(data) - num),
    color_ID, XMATCH(color, UNIQUE(color)),
    size, --LEFT(data, num - 2),
    size_ID, XMATCH(size, {11;17;5;24;36}),
    SORTBY(data, color_ID, 1, size_ID, 1)
)

     

    Also, if you run into the same problem with the colors not sorting in the preferred order, you could replace UNIQUE(color) with an array of colors in the order of your choosing. For example:

     

    =LET(
    data, FILTER(A2:A1000, A2:A1000<>""),
    num, SEARCH(" ", data),
    color, RIGHT(data, LEN(data) - num),
    color_ID, XMATCH(color, {"White";"Red";"Blue";"Yellow"}),
    size, --LEFT(data, num - 2),
    size_ID, XMATCH(size, {11;17;5;24;36}),
    SORTBY(data, color_ID, 1, size_ID, 1)
)

     

    As an aside, I've defined variables for each step in the examples shown above so they're easy to understand and follow, but the same formula(s) can also be written with fewer steps:

     

    =LET(
    data, FILTER(A2:A1000, A2:A1000<>""),
    num, SEARCH(" ", data),
    SORTBY(
        data,
        XMATCH(RIGHT(data, LEN(data) - num), {"White";"Red";"Blue";"Yellow"}), 1,
        XMATCH(--LEFT(data, num - 2), {11;17;5;24;36}), 1
    )
)

     

    I hope that works as expected. Cheers!

  • djclements's avatar
    djclements
    Bronze Contributor
    Jul 04, 2024

    ECD333285 If you are using Excel for MS365 or Excel for the web, you could try using the SORTBY function as follows:

     

    =LET(
    data, TOCOL(A2:A1000, 1),
    size, TEXTBEFORE(data, " "),
    color, TEXTAFTER(data, " "),
    color_ID, XMATCH(color, UNIQUE(color)),
    SORTBY(data, color_ID, 1, size, 1)
)

     

    Adjust the data range reference as needed.

     

    Sample Results

     

    Since you mentioned "the colors are in a specific order not based on alphabet", XMATCH was used to generate the color_ID for each color, based on the order in which they appeared in the list.

     

    Please note, this does not affect the order of the original data. Rather, it returns a copy of the data, sorted in a new location.

     

    EDIT: it's not actually necessary to break out the size in this scenario. Sorting by color_ID and data will produce the same results...

     

    =LET(
    data, TOCOL(A2:A1000, 1),
    color, TEXTAFTER(data, " "),
    color_ID, XMATCH(color, UNIQUE(color)),
    SORTBY(data, color_ID, 1, data, 1)
)

     

    • ECD333285's avatar
      ECD333285
      Copper Contributor
      Jul 05, 2024

      djclements  Thank you, I'm getting a NAME error right away on the range.  I have very little experience with 365 so I haven't figured out how I'm going about it incorrectly.

      • djclements's avatar
        djclements
        Bronze Contributor
        Jul 06, 2024

        ECD333285 The #NAME? error is a pretty strong indication that your version of Excel does not support one or more of the functions used in my first example. TOCOL, TEXTBEFORE and TEXTAFTER are currently only available in Excel for MS365 (subscription).

         

        If you are using Excel 2021, a compatible variant of the formula would be:

         

        =LET(
    data, FILTER(A2:A1000, A2:A1000<>""),
    color, RIGHT(data, LEN(data) - SEARCH(" ", data)),
    color_ID, XMATCH(color, UNIQUE(color)),
    SORTBY(data, color_ID, 1, data, 1)
)

         

        LET, FILTER, XMATCH, UNIQUE and SORTBY are all available in Excel 2021 and later versions, according to Microsoft documentation.

         

        If you are using Excel 2019 or an earlier version, add a helper column to your dataset to assign the ColorID by matching each color to a list or table of colors. For example:

         

        =MATCH(RIGHT(A2, LEN(A2) - SEARCH(" ", A2)), tblColors[Color], 0)

         

        ...where tblColors is a structured Excel table (created by going to Insert > Table on the ribbon).

         

        Table: tblColors

         

        Then, sort the data by going to Home > Sort & Filter > Custom Sort... and sort by ColorID, then by Size/Color:

         

        Home > Sort & Filter > Custom Sort...

         

        Custom Sort Results

         

        Please download the attached sample workbook, if needed, which contains all of the above-mentioned examples.

         

        EDIT: if a helper column is required, you could also just extract the Color...

         

        =RIGHT(A2, LEN(A2) - SEARCH(" ", A2))

         

        ...then use a Custom List to sort by Color, then by Size/Color.

         

        For more information, please see: https://support.microsoft.com/en-us/office/sort-data-using-a-custom-list... 

Resources