## Forum Discussion

# Sorting on multiples in same column

- Jul 10, 2024
ECD333285 No worries. The original formula sorted the

*data*alphanumerically, treating the sizes as text (anything thata "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*begins with**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!

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.

Since you mentioned "*t**he 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)
)
```

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.

- djclementsJul 06, 2024Bronze Contributor
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).Then, sort the data by going to

**Home > Sort & Filter > Custom Sort...**and sort by*ColorID*, then by*Size/Color*: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...

- ECD333285Jul 10, 2024Copper ContributorHi - Thanks very much the formula for Excel 2021 works just right! Another question arises in that when I include the additional sizes the order isn't controlled. I'm not clear what is used to order the sizes. I should have included this in the original post. The sizes I'm hoping to order as:

11

17

5

24

36

I'd like to understand how the sizes get sorted so I can substitute some different orders without having to bug the community ðŸ™‚

Thanks!- djclementsJul 10, 2024Bronze Contributor
ECD333285 No worries. The original formula sorted the

*data*alphanumerically, treating the sizes as text (anything thata "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*begins with**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!