G
Guest
I have around 300 columns with 46 rows in each column that I need to convert into one long column. Can someone help please
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
F
Frank Kabel
Hi
one way: use the following formula on a separate sheet in A1
=OFFET('sheet1'!$A$1,MOD(ROW()-1,46),INT((ROW()-1)/46))
and copy this down
one way: use the following formula on a separate sheet in A1
=OFFET('sheet1'!$A$1,MOD(ROW()-1,46),INT((ROW()-1)/46))
and copy this down
A
Alan Beban
Well, you don't have 300 columns because Excel has only 256 available.
If your data were in A1:IV46, would the second value in the one long
column be from A2 or B1?
Alan Beban
If your data were in A1:IV46, would the second value in the one long
column be from A2 or B1?
Alan Beban
G
Guest
Sorry about the delay
256 columns will work ok then. I needed roughly 300 columns.
The second value in the long column needs to be from A2. This would continue until A46 then B1, B2, B3...B46, C1...etc
256 columns will work ok then. I needed roughly 300 columns.
The second value in the long column needs to be from A2. This would continue until A46 then B1, B2, B3...B46, C1...etc
H
Harlan Grove
...
...
So down then right. See Frank Kabel's response. If you want to enter these
results starting (topmost) in a cell other than A1, say X99, adapt Frank's
formula to
=OFFSET(Your!$A$1,MOD(ROW()-ROW($X$99),46),INT((ROW()-ROW($X$99))/46))
...
The second value in the long column needs to be from A2. This would continue
until A46 then B1, B2, B3...B46, C1...etc.
So down then right. See Frank Kabel's response. If you want to enter these
results starting (topmost) in a cell other than A1, say X99, adapt Frank's
formula to
=OFFSET(Your!$A$1,MOD(ROW()-ROW($X$99),46),INT((ROW()-ROW($X$99))/46))