How to Filter Excel Data by Code and Sort Months Chronologically
Question details
The user needs to extract records matching a specific code from a dataset and sort the resulting data by calendar month (January to December), retaining all duplicates.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing and extracting specific categorical data while maintaining a strict chronological calendar month order instead of alphabetical order.
- Observed behavior
- Data needs to be filtered by a cell reference and automatically arranged in chronological month order (Jan-Dec) with grouped details intact.
Ensure your month data is consistently formatted (e.g., three-letter abbreviations like 'JAN' or full names) so the formulas or Power Query can correctly identify and sort them.
Use Dynamic Array Formulas in Excel 365
Leverage Excel 365's powerful dynamic arrays to filter and stack data automatically in calendar-month order.
If you are using Excel 365, you can utilize modern array functions like REDUCE, FILTER, and VSTACK. This method allows you to instantly extract and sort your dataset without relying on manual filtering or complex macros.
Click on the top-left cell where you want the filtered and sorted data to appear.
Enter the following formula, adjusting the ranges (A3:A82, B3:D82) and the criterion cell (G2) to match your dataset: =DROP(REDUCE("",{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},LAMBDA(a,x,IFERROR(VSTACK(a,FILTER(CHOOSECOLS(B3:D82,3,1,2),(A3:A82=G2)*(D3:D82=x),x)),""))),1)
Press Enter. The formula will automatically spill the matching records down the rows, grouped chronologically by month.
Filter and Sort Using Power Query
Ideal for older versions of Excel or large datasets, this method uses Power Query to merge with a chronological helper table.
Easily Filter and Sort Data in WPS Spreadsheet
WPS Spreadsheet provides robust data manipulation tools, including advanced filtering, custom sorting, and modern array compatibility to help you organize complex datasets effortlessly.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data you wish to filter.
- 2. Apply AutoFilter: Select your dataset, navigate to the 'Data' tab on the ribbon, and click 'AutoFilter'. Use the drop-down on your target column to filter by the specific code.
- 3. Use Custom Sort for Months: Click the filter arrow on your Month column, select 'Custom Sort', and choose the chronological month list (Jan, Feb, Mar...) from the Custom Lists options to sort the results instantly.
Frequently Asked Questions
Why does Excel sort months alphabetically instead of chronologically?
Excel treats month names (like 'Jan' or 'January') as basic text strings by default. To sort them chronologically, you must use a Custom Sort list, convert them to actual serial dates, or use a helper column containing month numbers.
Can I use the REDUCE and VSTACK formula in older Excel versions?
No, functions like REDUCE, VSTACK, and CHOOSECOLS are exclusive to Excel 365 and Excel for the Web. For older versions like Excel 2016 or 2019, the Power Query method or manual helper columns are the best alternatives.
How do I create a custom sort list for months in my spreadsheet?
Go to File > Options > Advanced > Edit Custom Lists. You can manually type the months in chronological order here. Once saved, you can use the standard Sort dialog to arrange your text-based months correctly.
