How to Combine Two Excel Sheets by Alternating Columns
Question details
The user needs to combine two parallel Excel worksheets into a single sheet by interleaving their columns sequentially, while preserving any blank columns in the source data.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating data from two identical-structure sheets (e.g., 84 columns each) where columns must be placed side-by-side alternately (Sheet A Col 1, Sheet B Col 1, Sheet A Col 2, Sheet B Col 2, etc.).
- Observed behavior
- The goal is to automatically interleave the columns from both sheets using a formula to avoid tedious manual copying and pasting, ensuring a perfectly alternating merged dataset.
Ensure both source sheets (e.g., 'Sheet A' and 'Sheet B') have the exact same number of rows and columns, and verify whether your actual data starts in column A or column B before applying the formula.
Use INDEX and COLUMN Formulas (For Data Starting in Column A)
Use a combination of IF, ISODD, INDEX, and COLUMN functions to automatically pull columns alternately from two source sheets starting from the first column.
This formula uses the column number of the output cell to determine which sheet to pull from. Odd-numbered columns pull from Sheet A, and even-numbered columns pull from Sheet B.
Create a new blank worksheet in your workbook where the combined data will be generated.
Select cell A1 in the new sheet and enter the following formula: =IF(ISODD(COLUMN()),INDEX('Sheet A'!$A:$CF,ROW(),(COLUMN()+1)/2),INDEX('Sheet B'!$A:$CF,ROW(),COLUMN()/2))&""
Press Enter. Click the fill handle (the small square in the lower-right corner of cell A1) and drag it down to fill the formula for all the required rows of your dataset.
With the column of formulas still selected, click and drag the fill handle to the right across the necessary number of columns (e.g., 168 columns if combining two 84-column sheets) to complete the merge.
Adjust Formula for Data Starting in Column B
If your data tables use column A for row headers and the data ranges actually start in column B (spanning B:CG), the formula range must be adjusted.
Seamlessly Combine Spreadsheets with WPS Spreadsheet
WPS Spreadsheet provides powerful formula calculation capabilities, allowing you to execute complex INDEX and COLUMN nesting flawlessly. It handles massive arrays with ease, making data consolidation quick and accurate.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing the two sheets you need to combine.
- 2. Create a Merging Sheet: Click the '+' icon at the bottom tab bar to add a new worksheet for your interleaved data.
- 3. Apply Formula and Fill: Paste the alternating column formula into cell A1, press Enter, and drag the fill handle down and right to instantly populate the combined data.
Frequently Asked Questions
Why does the formula return zeros for blank cells without the &"" addition?
By default, when a formula references an empty cell in Excel or WPS Spreadsheet, the application returns a 0. Adding the empty string &"" to the very end of the formula forces the output to be treated as text, displaying a true blank instead of a zero.
What if my sheets have a different number of columns, like 50 instead of 84?
You simply need to change the column range inside the INDEX functions. For example, if your data goes up to column AX (which is 50 columns), change the range $A:$CF to $A:$AX. You will then only need to drag the formula across 100 columns.
How do I remove the formulas and keep just the combined alternating data?
Select the entire merged data range and press Ctrl+C to copy it. Right-click the same selected area and choose 'Paste Values' (or use Paste Special > Values). This strips away the formulas, leaving only the static interleaved text and numbers.
