Stacking Many Columns into one Column

D

dawnandrea2018

New Member
Joined
Jun 12, 2018
Messages
4
Hi,
I have a sheet with "raw data" with many columns (74+) and i need to stack them into one specific column on another sheet. This specific column has data already in it and above it.
Basically, I need to take specific columns and stack them into one column on another worksheet where there is existing data.
Its not efficient for me to copy and paste the data more than 74+. Every year there is also an increase in number of people, it may be 100 people for 2018.
For example the sheets kind of look like this:

Raw data worksheet:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Question code[/TD]
[TD]Question[/TD]
[TD]Person 1[/TD]
[TD]Person 2[/TD]
[TD]Person 3[/TD]
[TD]Person 4[/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1a[/TD]
[TD]Worked on..[/TD]
[TD]1[/TD]
[TD]66[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1b[/TD]
[TD]Put together..[/TD]
[TD]3[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1c[/TD]
[TD]Made...[/TD]
[TD]4[/TD]
[TD]13[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[/TR]
</tbody>[/TABLE]


Dump Data sheet looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Year[/TD]
[TD]Indicator[/TD]
[TD]Response[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2017[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2017[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2017[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2017[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The dump sheet should like this afterwards, with responses stacked on top of each other:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Year[/TD]
[TD]Indicator[/TD]
[TD]Response[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2017[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2017[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2017[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2017[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2017[/TD]
[TD]...[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2017[/TD]
[TD]..[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2017[/TD]
[TD]..[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2017[/TD]
[TD]..[/TD]
[TD]66[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2017[/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2017[/TD]
[TD][/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2017[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]2017[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]2017[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]2017[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I would really appreciate any help. Thanks!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
See if this Will do what you want.
Code: 
Sub t()
Dim i As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Raw Data") 'Edit sheet name
Set sh2 = Sheets("Dump Data") 'Edit sheet name
    For i = 3 To sh1.Cells(2, Columns.Count).End(xlToLeft).Column
        With sh1
            .Range(.Cells(2, i), .Cells(Rows.Count, i).End(xlUp)).Copy sh2.Cells(Rows.Count, 3).End(xlUp)(3)
        End With
    Next
    sh2.Range("A3", .Cells(Rows.Count, 3).End(xlUp).Offset(, -2)) = sh2.Range("A2").Value
End Sub

Note that you will need to validate the sheet names where noted in the code.
 
Last edited:
Upvote 0
Thanks!

I forgot to mention in my question that there is a table below in the raw data worksheet, and it copies this when I run the code.
How would I change this code to reflect this?

To rephrase my question, I have a range of data (i.e. F2:CB33) where I want to copy column by column and stack this in another worksheet (dump worksheet).
The dump worksheet already has existing data - I want this new data to be copied at the end of the existing data in column F. At the moment it is at F2915.
 
Upvote 0
JLGWhiz said:
See if this Will do what you want.
Code: 
Sub t()
Dim i As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Raw Data") 'Edit sheet name
Set sh2 = Sheets("Dump Data") 'Edit sheet name
    For i = 3 To sh1.Cells(2, Columns.Count).End(xlToLeft).Column
        With sh1
            .Range(.Cells(2, i), .Cells(Rows.Count, i).End(xlUp)).Copy sh2.Cells(Rows.Count, 3).End(xlUp)(3)
        End With
    Next
    sh2.Range("A3", .Cells(Rows.Count, 3).End(xlUp).Offset(, -2)) = sh2.Range("A2").Value
End Sub

Note that you will need to validate the sheet names where noted in the code.
Click to expand...

Also, what does this line do:

dataSheet.Range("A3", .Cells(Rows.Count, 3).End(xlUp).Offset(, -2)) = dataSheet.Range("A2").Value

I have been trying to get it, but I have no clue.
 
Upvote 0
dawnandrea2018 said:
Also, what does this line do:

dataSheet.Range("A3", .Cells(Rows.Count, 3).End(xlUp).Offset(, -2)) = dataSheet.Range("A2").Value

I have been trying to get it, but I have no clue.
Click to expand...

The line of code above was intended to copy your year in column A down as far as your data in the other columns by using the value in cell A2.
I forgot to mention in my question that there is a table below in the raw data worksheet
Click to expand...
To accomodate the table and eliminate it from the copy action, the exact range address of the table would need to be known, and if it will always be in that same address. If the table address varies from session to session then some other means of identifying where it is, like a constant and unique header value would have to be known. Otherwise, I would not know how to tell VBA to avoid copying the table.
 
Upvote 0
I have a range of data (i.e. F2:CB33) where I want to copy column by column and stack this in another worksheet (dump worksheet).
The dump worksheet already has existing data - I want this new data to be copied at the end of the existing data in column F.
Click to expand...
Based on the above, I think the changes in red font would do what you want.
Code: 
Sub t()
Dim i As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Raw Data") 'Edit sheet name
Set sh2 = Sheets("Dump Data") 'Edit sheet name
    For i = 3 To sh1.Cells(2, Columns.Count).End(xlToLeft).Column
        With sh1
            .Range(.Cells(2, i), .Cells[COLOR=#FF0000](33,[/COLOR] i).End(xlUp)).Copy sh2.Cells(Rows.Count, [COLOR=#FF0000]6[/COLOR])[COLOR=#FF0000].[/COLOR]E[COLOR=#FF0000][/COLOR]nd(xlUp)(3)
        End With
    Next
    sh2.Range("A3", .Cells(Rows.Count, 3).End(xlUp).Offset(, -2)) = sh2.Range("A2").Value
End Sub
 
Upvote 0
JLGWhiz said:
Based on the above, I think the changes in red font would do what you want.
Code: 
Sub t()
Dim i As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Raw Data") 'Edit sheet name
Set sh2 = Sheets("Dump Data") 'Edit sheet name
    For i = 3 To sh1.Cells(2, Columns.Count).End(xlToLeft).Column
        With sh1
            .Range(.Cells(2, i), .Cells[COLOR=#FF0000](33,[/COLOR] i).End(xlUp)).Copy sh2.Cells(Rows.Count, [COLOR=#FF0000]6[/COLOR])[COLOR=#FF0000].[/COLOR]End(xlUp)(3)
        End With
    Next
    sh2.Range("A3", .Cells(Rows.Count, 3).End(xlUp).Offset(, -2)) = sh2.Range("A2").Value
End Sub
Click to expand...

For some reason it skips responses when it pastes..
 
Upvote 0
Can you post a screen shot or mock up of your sheet, or provide a link to your file on a share server. I am not sure that I have a good concept of what your sheet looks like.
 
Upvote 0
I just noticed that his was missing a qualifier.

Code: 
sh2.Range("A3", [COLOR=#B22222]sh2[/COLOR].Cells(Rows.Count, 3).End(xlUp).Offset(, -2)) = sh2.Range("A2").Value
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Drop down list with selection containing Alpha and Numeric characters not working
Replies
1
Views
126
myall_blues
myall_blues
J
  • Question Question
Stacked Clustered Column Chart
Replies
0
Views
273
johnnypp
J
A
  • Solved
Grabbing the latest data from a Column based on a cell
Replies
4
Views
479
PeteWright
PeteWright
H
  • Solved
Delete all rows including cells in column "A" that are identical to the values in cells "D1", "E1","F1" and "A6".
2
Replies
10
Views
586
Peter_SSs
Peter_SSs
P
  • Question Question
User to select data
Replies
2
Views
386
pastorkc
P

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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