How to Combine Two Excel Tables and Sort Months in Calendar Order
Question details
The user needs to merge two Excel tables containing different months without common rows into a single list sorted in calendar order (January to December).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Combining disjointed sets of monthly data from multiple tables and generating a consolidated report sorted chronologically rather than alphabetically.
- Observed behavior
- Using standard VLOOKUP fails to consolidate the complete list in chronological order because the tables lack common rows, and Excel sorts text-based month names alphabetically.
Ensure both of your source tables have identical column headers. It is also highly recommended to create a separate reference table that lists the months from January to December alongside a corresponding index number (1 to 12) to facilitate chronological sorting.
Use Power Query to Append Tables and Sort by a Month Index
Power Query is the most robust and scalable method for combining tables and applying custom chronological sorting without writing complex formulas.
This method involves loading your tables into Power Query, appending them into one list, and merging them with a helper table that defines the chronological order of the months.
Select each of your source tables, go to the Data tab, and click 'From Table/Range' to load them into Power Query. Repeat this for your 1-12 Month Order helper table.
In the Power Query Editor, select your first table query, click 'Append Queries' on the Home tab, and select your second table to combine them into one dataset.
Select the Month column in your appended query, right-click the header, and choose Transform > Capitalize Each Word. This ensures exact text matching when joining tables.
Click 'Merge Queries' on the Home tab. Select the appended table and your Month Order table, highlighting the 'Month' column in both to join them. Once merged, click the expand icon on the new column and select only the 'Index' field.
Click the drop-down arrow on the expanded Index column and select 'Sort Ascending' to organize the months from January to December. Right-click the Index column to remove it, then click 'Close & Load' to output the combined table back to your workbook.
Use Nested VLOOKUP with IFERROR for Small Datasets
If you have a fixed, simple dataset, you can set up a static list of months and use nested VLOOKUP formulas to pull data consecutively from either table.
Combine and Sort Data Seamlessly in WPS Spreadsheet
WPS Spreadsheet provides powerful data consolidation tools and advanced formula support, making it incredibly easy to merge separate datasets and sort them chronologically without a steep learning curve.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the separated tables.
- 2. Create a master date column: List your months chronologically in a new column to act as your layout guide.
- 3. Insert consolidation formulas: Use the intuitive formula builder to insert your nested VLOOKUP or IFERROR functions to pull data effortlessly.
- 4. Use Data Consolidate: Alternatively, navigate to the Data tab and use the 'Consolidate' feature for a fast, formula-free data merge.
Frequently Asked Questions
Why does Excel sort my months alphabetically instead of chronologically?
Excel treats typed month names (like 'January', 'February') as standard text, so default sorting organizes them from A to Z. To sort chronologically, you must either format them as actual Date values or use a custom sort list or an index helper column.
Can I use XLOOKUP instead of nested VLOOKUP formulas to combine tables?
Yes, XLOOKUP greatly simplifies this process. You can use its built-in 'if_not_found' parameter to nest a second XLOOKUP directed at the second table, entirely removing the need for an external IFERROR function.
What happens if both tables contain the same month?
If you use the nested VLOOKUP method, the formula will only return the value from the first table it searches. If you need to combine or add overlapping values, you should use the SUMIFS function instead, or utilize the 'Group By' feature in Power Query.
