How to Combine Lists from Two Excel Sheets in Order
Question details
The user needs to merge rows from two different worksheets into a single list, ensuring that all records from the first sheet appear before those from the second sheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Combining records sequentially from multiple worksheets into a unified master list.
- Observed behavior
- Standard IF and VLOOKUP formulas fail to append rows sequentially, failing to produce the expected combined list.
Ensure both of your source lists have identical column headers and are formatted as Excel Tables (Ctrl + T) before attempting to merge them.
Use Power Query to Append Tables
Power Query is the most reliable method to combine two lists sequentially, completely bypassing the complexity and limitations of IF and VLOOKUP formulas.
Power Query allows you to easily stack tables on top of each other using the Append feature. This ensures that the records from the first sheet are followed seamlessly by the records from the second sheet.
Highlight the data in your first worksheet and press Ctrl+T to format it as a Table. Repeat this step for the data in your second worksheet.
Select a cell in your first table, go to the Data tab, and click 'From Table/Range' to load it into the Power Query Editor. Close the editor by selecting 'Close & Load To...' and choose 'Only Create Connection'. Do the same for the second table.
On the Excel ribbon, navigate to Data > Get Data > Combine Queries, and select 'Append'.
In the Append dialog box, select 'Two tables'. Choose your first table from the 'Primary table' dropdown and your second table from the 'Table to append' dropdown to set the correct order, then click OK.
The Power Query Editor will open showing the combined data. Click the 'Close & Load' button in the top-left corner to output the merged list onto a new worksheet.
Combine Worksheets Easily with WPS Spreadsheet
WPS Office provides a built-in 'Merge Worksheets' toolkit that allows you to combine multiple sheets into one sequentially, without writing complex formulas or needing to learn Power Query.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the sheets you want to merge.
- 2. Access the Merge Tool: Navigate to the 'Data' tab on the top ribbon and click on the 'Merge Worksheets' icon.
- 3. Select Merge Option: Choose 'Merge multiple worksheets into a single worksheet' from the available toolkit options.
- 4. Choose Sheets to Combine: Check the boxes for the specific sheets you wish to combine and arrange them in the correct sequence.
- 5. Execute the Merge: Click 'Merge' to automatically extract and combine the records into a newly generated worksheet.
Frequently Asked Questions
Can I combine lists if they have different column headers?
Yes, but it is not recommended. Power Query will match columns by their exact header names. If the headers differ, Power Query will create separate columns for the unmatched headers and fill the empty spaces with 'null' values. Always standardize headers before merging.
Why didn't my VLOOKUP formula work for combining lists?
VLOOKUP is designed to search for and extract specific matching data points based on a unique identifier across tables. It is not capable of stacking or appending entire rows of data sequentially into a single master list.
Is it possible to append more than two tables at once?
Yes. When you open the Append dialog box in Power Query (Data > Get Data > Combine Queries > Append), you can select the 'Three or more tables' option to sequence and combine multiple tables simultaneously.
