Taking a list that fills multiple columns and turn it into a list with only 2 columns

[mcfurlong1]

I currently have a list of clients where in some cases, the clients have multiple client numbers which are in different columns. I need to move those with multiple client numbers to have the client number on it's own row. My dataset currently looks like this:

CLIENT	CODE 1	CODE 2	CODE 3	CODE 4
Alina Smith	1​	5​	9​	12​
Ashley Ray	3​	6​
Ben Goldsmith	2​
Bill Luther	8​
Brett Beavers	7​	4​

And I want it to look like this:

CLIENT	CODE
Alina Smith	1​
Alina Smith	5​
Alina Smith	9​
Ashley Ray	3​
Ashley Ray	6​
Ben Goldsmith	2​
Bill Luther	8​
Brett Beavers	7​
Brett Beavers	4​

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub mcfurlong1()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, rr As Long
   
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 2)
   rr = 1
   Nary(rr, 1) = "Client"
   Nary(rr, 2) = "Code"
   For r = 2 To UBound(Ary)
      For c = 2 To UBound(Ary, 2)
         If Ary(r, c) <> "" Then
            rr = rr + 1
            Nary(rr, 1) = Ary(r, 1)
            Nary(rr, 2) = Ary(r, c)
         End If
      Next c
   Next r
   Sheets("Sheet2").Range("A1").Resize(rr, 2).Value = Nary
End Sub

Change sheet names to suit