How to convert a repeating row of information to fit in 3 columns

D

dawn

I have a spreadsheet with 300 rows and colums up to IR.

The columns repeat- #, description, size. I need to convert the spreadsheet
sp that it is 3 colums wide only with the title above and have all of the
information drop down into these.
 
M

Max

dawn said:
I have a spreadsheet with 300 rows and colums up to IR.
The columns repeat- #, description, size. I need to convert the spreadsheet
so that it is 3 columns wide only with the title above and have all of the
information drop down into these.
Click to expand...

Here's one quick play to try ..

Assuming source data is in sheet: X, cols A to IR (84 sets),
col headers in row1, data in row2 to 301

In another sheet: Y (say)
With 1 set of headers in A1:C1, put:

In A2:
=OFFSET(X!$A$2,MOD(ROW(A1)-1,300),INT((ROW(A1)-1)/300)*3)

In B2:
=OFFSET(X!$B$2,MOD(ROW(A1)-1,300),INT((ROW(A1)-1)/300)*3)

In C2:
=OFFSET(X!$C$2,MOD(ROW(A1)-1,300),INT((ROW(A1)-1)/300)*3)

Then just select A2:C2, copy down to C25201
(84 sets x 300 rows per set = 25,200)

The above will return the desired results.

If required, select cols A to C and freeze the values in Y
via an in-place:
Copy > Paste special > Check "Values" > OK

For easy try-out, just rename your actual source sheet to: X,
then copy n paste the 3 formulas as-is into a new sheet.
Get it working first. Then just restore / rename
the source sheet back to its former name, and
leave it to Excel to auto-update the sheetname in the formulas.
 
Top