how to transform table into one single column

[Swissshield]

hi all,

I have a table with 36 rows and 100 columns. In order to update a key in SAP I have to fill this into one single column. E.g. A1-A36,B1-B36,C1... etc.

Is there any simple formula combination to simplify this since I have to do this several times.

I've been trying to google this but I couldn't find anything sufficient, neither in this forum.

Thanks for you help!

 

[NARAYANK991]

Hi ,

Try this :

=OFFSET(Base!$A$1,MOD(ROW($A1)-1,36),INT((ROW($A1)-1)/36))

Base is the sheet tab which has your table.

Put the above formula on a different sheet , in cell A1 , and copy down as many rows as you want.

Narayan

 

[oldchippy]

Hi Swissshield,

Welcome to the forum, try this, insert a couple of columns so your data is in now in columns C1 to CX (that should be 100 columns wide), then in A1 put this and drag downwards

=OFFSET($C$1,INT((ROW()-1)/100),MOD(ROW()-1,100))

Sorry misread your question - you wanted it column on top of column

 

[Swissshield]

Hi ,

Try this :

=OFFSET(Base!$A$1,MOD(ROW($A1)-1,36),INT((ROW($A1)-1)/36))

Base is the sheet tab which has your table.

Put the above formula on a different sheet , in cell A1 , and copy down as many rows as you want.

Narayan

WOW Thanks a lot Narayan! this worked perfect!
Best,
Swissshield

 

[Swissshield]

Hi Swissshield,

Welcome to the forum, try this, insert a couple of columns so your data is in now in columns C1 to CX (that should be 100 columns wide), then in A1 put this and drag downwards

=OFFSET($C$1,INT((ROW()-1)/100),MOD(ROW()-1,100))

Sorry misread your question - you wanted it column on top of column

Thanks Oldchippy! you guys are