One Entry to Multiple Rows

[Pandaura]

I have data that looks like this:
X1 | Y1 Y2 Y3 Y4
X2 | Y4 Y5 Y6 Y7

And I need to get to:
X1 | Y1
X1 | Y2
X1 | Y3
X1 | Y4
X2 | Y4
...... etc.

I can change the 2nd row's entries to more columns, but that doesn't seem to
get me much closer to the needed format (and there are thousands of lines so
I'd rather not do it manually). Any ideas?

 

[Don Guillett]

should do it. change mc to suit
'=====
Option Explicit
Sub lineemup()
Dim mc As Long
Dim mr As Long
Dim i As Long
Dim lc As Long

mc = 3 'col c
mr = 1
For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row
lc = Cells(i, Columns.Count). _
End(xlToLeft).Column - mc
Cells(i, mc).Resize(, lc).Copy
Cells(mr, mc - 1).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
mr = mr + lc
Next i
Application.CutCopyMode = False
End Sub