HELP how to stack multiple columns into one column. Using (formula or vba code)

[usui]

Hi masters,

Can anyone help me with a formula for stacking multiple columns data into one column. wherein it copies per column not per row.

sample here:

I hope anyone has an idea using excel 2016 here..please help me

 

[Max1616]

Yes please, the only data present in column H is the header( Result here). If you can move it to H2 that you be very great sir.

Here's the updated code:

Sub Consolidate()
Dim lrow As Long
Dim i As Long
Dim j As Long
Dim DestCol As Integer
Dim NumOfCol As Integer
Dim DestCounter As Long

DestCol = 8 'Put the column number you'd like the results to go
NumOfCol = 5 'Put the number of columns that contain data

DestCounter = 2

For i = 2 To NumOfCol + 1
    lrow = Cells(Rows.Count, i).End(xlUp).Row
    For j = 2 To lrow
        If Cells(j, i) <> "" Then
            Cells(j, i).Copy Cells(DestCounter, DestCol)
            DestCounter = DestCounter + 1
        End If
    Next j
Next i

End Sub

 

[jolivanes]

Which Row is the top Row with data to be copied and pasted? Looks like you have a narrow Row as Row2
Does the data start in B2 or B3?
If it starts in B2, the suggestion in Post #14 will work. Another possible problem could be tat if the Cells below and to the right are not really empty cell.
If you run the suggested macro from Post #14 and when finished, hold down the Ctrl key and press End (Ctrl+End), where do you end up?

 

[usui]

Here's the updated code:

Sub Consolidate()
Dim lrow As Long
Dim i As Long
Dim j As Long
Dim DestCol As Integer
Dim NumOfCol As Integer
Dim DestCounter As Long

DestCol = 8 'Put the column number you'd like the results to go
NumOfCol = 5 'Put the number of columns that contain data

DestCounter = 2

For i = 2 To NumOfCol + 1
    lrow = Cells(Rows.Count, i).End(xlUp).Row
    For j = 2 To lrow
        If Cells(j, i) <> "" Then
            Cells(j, i).Copy Cells(DestCounter, DestCol)
            DestCounter = DestCounter + 1
        End If
    Next j
Next i

End Sub

Hi Sir, sorry for the late reply. The code works like a charm thank you so much