Converting many rows into a single column

[Limey2632]

Hi Folks,
I apologize for my title. I'm not exactly sure what to call it. It's not transposing exactly as I want everything in a single column, not multiple columns.
I have to provide our company planners data in a particular format. I have the data in a spreadsheet where the related info is in rows. There is an ID#, a Product name, Sku1, Sku2 and Sku3

The data in the example below is different from above but it demonstrates the layout required.
The sku numbers are placed under the product name in a single column and the ID# is in a column to the left. The column ID# shows for each component of the original row.
For example, the first 3 rows would have come from 1 RHYTHM 37869 745T7. As can be seen, the ID# in col.A beside the data in B is "1".

Is this possible to do in Excel with either formula(s), VBA or other technique?

Many thanks,
Limey

 

[Limey2632]

Likely a shorter way, but this might work and place the "stacked" data in columns G and H respectively

Sub StackID()
Dim lr As Long, i As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
r = 2

 For i = 2 To lr
  Range("G" & r & ":G" & r + WorksheetFunction.CountA(Range("C" & i & ":E" & i))) = Cells(i, "A")
  Cells(r, "H") = Cells(i, "B")
  r = r + 1
  If Not IsEmpty(Cells(i, "C")) Then
   Cells(r, "H") = Cells(i, "C")
   r = r + 1
   End If
  If Not IsEmpty(Cells(i, "D")) Then
   Cells(r, "H") = Cells(i, "D")
   r = r + 1
   End If
  If Not IsEmpty(Cells(i, "E")) Then
   Cells(r, "H") = Cells(i, "E")
   r = r + 1
  End If
 
 Next i
 
End Sub

@kweaver Thank you so much for the Macro! The first version you posted worked like a dream. I can't thank you enough.
This is the result. Perfect.

 

[kweaver]

Happy to hear that worked.

 

[Limey2632]

Likely a shorter way, but this might work and place the "stacked" data in columns G and H respectively

Sub StackID()
Dim lr As Long, i As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
r = 2

 For i = 2 To lr
  Range("G" & r & ":G" & r + WorksheetFunction.CountA(Range("C" & i & ":E" & i))) = Cells(i, "A")
  Cells(r, "H") = Cells(i, "B")
  r = r + 1
  If Not IsEmpty(Cells(i, "C")) Then
   Cells(r, "H") = Cells(i, "C")
   r = r + 1
   End If
  If Not IsEmpty(Cells(i, "D")) Then
   Cells(r, "H") = Cells(i, "D")
   r = r + 1
   End If
  If Not IsEmpty(Cells(i, "E")) Then
   Cells(r, "H") = Cells(i, "E")
   r = r + 1
  End If
 
 Next i
 
End Sub

Many thanks @kweaver I thought I already replied to you but I don't see it posted. The first version works perfectly for what I need. Thank you so very much. You made a very tedious and time consuming task a breeze. I'm truly grateful!
(edit: Oh, now I see my previous reply )