How to paste multiple columns to single row

S

shifan

New Member
Joined
Mar 29, 2012
Messages
6
Hi Guys, I have huge amount of data to be copied from vertical (two lines) to horizontal (single line). would appreciate your expertise.

Thank you in advance
[TABLE="width: 524"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Column1
[/TD]
[TD="width: 75, bgcolor: transparent"]column1
[/TD]
[TD="width: 78, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 40, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]100
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]200
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]150
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]190
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]350
[/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 217, bgcolor: transparent, colspan: 3"]Above data should be copied in single line
[/TD]
[TD="bgcolor: transparent, align: right"]100
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, align: right"]200
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent, align: right"]150
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]190
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent, align: right"]350
[/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Why do you show two column 1's

Excel only has one column 1

We normally use the terms column and Rows not lines.

And what row do you want this data copied to. And do we start copying into Row(5) column (C)
 
Upvote 0
shifan said:
Hi Guys, I have huge amount of data to be copied from vertical (two lines) to horizontal (single line).
Click to expand...
See if this is any use. I have assumed data is in columns A:B, starting at row 2. Results shown at right.
Test in a copy of your workbook.
Code: 
Sub ColumnsToRow()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To 1, 1 To UBound(a) * 2)
  For i = 1 To UBound(a)
    For j = 1 To UBound(a, 2)
    k = k + 1
    b(1, k) = a(i, j)
    Next j
  Next i
  Range("D2").Resize(, UBound(b, 2)).Value = b
End Sub


Book1
ABCDEFGHIJKLMNOP
1
2100210022004150220119053506
32004
41502
5201
61905
73506
8
Columns to Row
 
Upvote 0
If we are to assume that we are dealing with column A and B
And assuming we start in row(2)

Try this:
Code: 
Sub Test()
'Modified 5/6/2018 11:10 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim s As Long
Dim z As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
s = 4
z = 5
    For i = 2 To Lastrow
        Cells(2, s).Value = Cells(i, 1).Value
        Cells(2, z).Value = Cells(i, 2).Value
        z = z + 2
        s = s + 2
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Peter_SSs said:
See if this is any use. I have assumed data is in columns A:B, starting at row 2. Results shown at right.
Test in a copy of your workbook.
Code: 
Sub ColumnsToRow()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To 1, 1 To UBound(a) * 2)
  For i = 1 To UBound(a)
    For j = 1 To UBound(a, 2)
    k = k + 1
    b(1, k) = a(i, j)
    Next j
  Next i
  Range("D2").Resize(, UBound(b, 2)).Value = b
End Sub

ABCDEFGHIJKLMNOP

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Columns to Row​
Click to expand...
Thank you
 
Upvote 0
Assuming your data starts in cell A2, here is a non-looping macro that should also work...
Code: 
[table="width: 500"]
[tr]
	[td]Sub DoubleColumnToSingleRow()
  Dim Arr As Variant
  Arr = Split(Join(Evaluate(Replace("TRANSPOSE(A2:A#&""|""&B2:B#)", "#", Range("A2").End(xlDown).Row)), "|"), "|")
  With Range("D2").Resize(, UBound(Arr) + 1)
    .Value = Arr
    .Value = .Value
  End With
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
shifan said:
Thank you Peter, it was a real time saver.
Click to expand...
You are very welcome. :)

BTW ...
1. Best not to fully quote longish posts as it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only.

2. You said that you had a "huge" amount of data. I'm wondering just how much? Of course Excel only has 16,000+ columns so I assume that your data couldn't be more than about 8,000 rows which actually isn't very huge when you consider Excel has over 1,000,000 rows. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Latest Data Pull-up from the corresponding date-Single party
Replies
3
Views
126
Sergius
S
A
  • Solved
Count multiples rows and ans in a single row and column
2
Replies
13
Views
148
Peter_SSs
Peter_SSs
R
  • Question Question
How to Paste a Single Column of ListBox Values to Sheet Across a Row
Replies
4
Views
212
Akuini
Akuini
O
  • Solved
I want it all! Using single lookup command to return both multiple Rows and Columns
Replies
4
Views
463
Fluff
Fluff
S
  • Solved
Vertical Sparklines Bar Graph linked to cells - can you change Max value, using a Cell Reference ?
2
Replies
19
Views
647
StrawberryDreams
S

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top