Excel Formula to Copy Data from Every Nth Row to Another Column
Question details
The user needs an Excel formula to copy or extract data from specific row blocks (e.g., rows 12-22) and place them alongside earlier rows (e.g., rows 1-11) in a new column.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reorganizing datasets by shifting chunks of rows (every Nth row or block) into adjacent columns to compare or align data.
- Observed behavior
- The goal is to accurately shift rows alongside an existing column using a dynamic formula that accounts for header presence and can scale to larger datasets.
Determine the exact range of your source data, whether your worksheet includes a header row, and the block size (N) of the rows you want to move.
Use INDEX and ROW Formula (For Data With Headers)
Best for standard datasets that include a header row, automatically adjusting row extraction while ignoring the header.
This formula uses the INDEX and ROW functions to dynamically pull data in blocks. It calculates the row position based on a block size (in this case, 11) and extracts the targeted cells.
Click on the destination cell next to your first data row (e.g., D2, assuming row 1 is a header).
Type the following formula: =IFERROR(INDEX($C$13:$C$23,ISODD(INT((ROW($C1)-1)/11)+1)*1),"")
Ensure that $C$13:$C$23 accurately reflects the source data you want to move. If your block size is different, change the number 11 to your specific block size.
Press Enter, then click and drag the fill handle down to copy the formula to the remaining rows.
Use INDEX and ROW Formula (For Data Without Headers)
Use this variation when your dataset starts immediately from row 1 without any title or header row.
Reorganize Your Data Efficiently with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas like INDEX and ROW, allowing you to manipulate and reorganize large datasets seamlessly without performance lags.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
- 2. Locate source and destination: Identify the exact range of your source data and choose the new column where the data will be copied.
- 3. Apply the formula: Type the INDEX and ROW formula exactly as you would in Microsoft Excel.
- 4. Auto-fill the column: Double-click or drag the fill handle to instantly reorganize your data blocks across hundreds of rows.
Frequently Asked Questions
How do I adjust the formula if my row block size is different?
Change the number '11' in the formula segment `(ROW($C1)-1)/11` to match your exact block size. For example, if you want to copy every 5th row, change it to 5.
Can this formula handle datasets with more than 500 rows?
Yes. To scale the formula for larger datasets, simply adjust the absolute range in the INDEX function (e.g., from `$C$13:$C$23` to `$C$13:$C$600`) and copy the formula down further.
Why does the formula use the ROW($C1) reference?
The `ROW($C1)` acts as a counter that evaluates to 1 in the first cell, 2 in the second, and so on as you drag it down. This dynamic counting is essential for calculating which nth row to extract from the source array.
