How to Sort Excel Items by Custom Color and Size Orders
Question details
The user needs to sort a list of combined text values by specific custom sequences for colors and sizes, rather than the default alphabetical or numeric sorting.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing lists of inventory or items where each cell contains a combined size and color attribute (e.g., '5-inch White' or '11-inch Red') and requires a specific logical sorting sequence.
- Observed behavior
- Standard sorting functions arrange the text strings alphabetically or numerically, which fails to align with the required custom color and size hierarchies.
Ensure your spreadsheet software supports dynamic array functions such as LET, XMATCH, and SORTBY, which are available in Microsoft 365, Excel for the web, and modern versions of WPS Office.
Use Advanced Array Formulas for Custom Sorting
Extract the size and color data dynamically using text functions, assign a custom sort key via XMATCH, and sort the list seamlessly without altering your original data.
By leveraging the LET function, you can define variables for size and color within a single formula. TEXTBEFORE and TEXTAFTER help split the combined text strings, while XMATCH assigns a specific rank based on your custom arrays.
Click on an empty cell where you want the sorted list to generate.
Input the formula: =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)). Replace A2:A1000 with your actual data range.
To force a specific color order, replace UNIQUE(color) in the formula with a custom array. For example: {"White";"Red";"Blue";"Yellow"}.
Similarly, to use a fixed size order, you can create a size key by modifying the formula to include XMATCH(size,{11;17;5;24;36}). Press Enter to execute.
Use Helper Columns and the Custom Sort Dialog
If you are using an older version of Excel that does not support dynamic array formulas, you can split the text into helper columns and use the Custom Lists sorting feature.
Sort Data with Advanced Functions in WPS Spreadsheet
WPS Office provides robust spreadsheet capabilities, including powerful text and dynamic array functions to help you manage, split, and sort complex datasets effortlessly.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your combined item list.
- 2. Apply sorting formulas: Select an empty cell and enter your custom SORTBY or XMATCH formulas to dynamically organize the data.
- 3. Use Custom Sort options: Alternatively, utilize the Data > Sort menu to set up custom lists directly from the intuitive ribbon interface.
Frequently Asked Questions
Can I sort by more than two custom criteria at once?
Yes, the SORTBY function allows you to add multiple sorting arrays. You simply append additional sorting criteria and their corresponding sort order indicators (1 for ascending, -1 for descending) to the end of the formula.
What if my data uses a hyphen instead of a space to separate size and color?
If your delimiter is different, you must change the delimiter argument in the TEXTBEFORE and TEXTAFTER functions. Replace the space (" ") with your specific character, such as a hyphen ("-").
Are these dynamic array formulas compatible with older versions of Excel?
No, functions like LET, XMATCH, and TEXTBEFORE are exclusive to modern spreadsheet software (Microsoft 365, Excel for the web, and updated WPS Office). For older versions, you should use the Helper Columns method with Custom Sort.
How do I save a custom sort order for future spreadsheets?
You can save custom lists permanently by navigating to File > Options > Advanced > Edit Custom Lists. Once added, you can select this list anytime you use the standard Data > Sort feature.
