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

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.

"dawn" wrote:
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.

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thankyou I will try this right now and let you know.

"Max" wrote:

"dawn" wrote:
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.

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"dawn" wrote:
Thank you I will try this right now and let you know.

You're welcome !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---