How do I merge several columns of data into one long column?

H

Homer

I have around 300 columns with 46 rows in each column that I need to convert into one long column. Can someone help please
 
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
 
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
 
H

homer

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
 
H

Harlan Grove

...
...
The second value in the long column needs to be from A2. This would continue
until A46 then B1, B2, B3...B46, C1...etc.
Click to expand...

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove Identical words 0
How to center adjust text above and image in a two column document. 1
Need help merging Excel into Publisher Booklet w 2 columns 0
Merging from an Excel file with graphics 1
Format column widths from one table to a separate table on same page 1
Booklet in Word, Merge Directory from Excel 0
Need Help with a VBA subroutine 0
Mail Merge Directory Conditional Page Break 0

Top