transpose matrix in one column?

Hi,
I'm not able to program VBA, but I don't think what I need can be anchieved only by standard formulas.

I have some rows like

A 1 2 3 4 5
B 1 2 3 4 5..100
C 1 2 3 4 5..299

(where numbers are random, and also letters could appear, like "A 3 45 45 2 xx a m")

what I would like to get is something like
A 1
A 2
..
A 5
B 1
B 2
..
B 100
C 1
C 2
..
C 299

so, repeating the row label for each column with characters on the original row.

Can you please help me?
Thanks a lot!!

It is difficult to see the data in text format. If this code does not work, we need to see your sample.

Sub trans()
Dim x, y(), i&, j&, k&
x = Sheets("Sheet1").Range("A2").CurrentRegion.Value
ReDim y(1 To UBound(x, 1) * UBound(x, 2), 1 To UBound(x, 2))
For i = 1 To UBound(x, 1)
    For j = 2 To UBound(x, 2)
         If Len(x(i, j)) Then
            k = k + 1
           y(k, 1) = x(i, 1)
           y(k, 2) = x(i, j)
        End If
    Next j
Next i

With Sheets.Add
    .UsedRange.ClearContents
    .Range("A1").Resize(k, UBound(x, 2)).Value = y()
    .Activate
End With
End Sub

It is difficult to see the data in text format. If this code does not work, we need to see your sample.

Sub trans()
Dim x, y(), i&, j&, k&
x = Sheets("Sheet1").Range("A2").CurrentRegion.Value
ReDim y(1 To UBound(x, 1) * UBound(x, 2), 1 To UBound(x, 2))
For i = 1 To UBound(x, 1)
    For j = 2 To UBound(x, 2)
         If Len(x(i, j)) Then
            k = k + 1
           y(k, 1) = x(i, 1)
           y(k, 2) = x(i, j)
        End If
    Next j
Next i

With Sheets.Add
    .UsedRange.ClearContents
    .Range("A1").Resize(k, UBound(x, 2)).Value = y()
    .Activate
End With
End Sub

(by the way, the result should be put in a new sheet, next to the original)
That was the original code did

Sub trans()
Dim x, y(), i&, j&, k&
x = Sheets("IPs").Range("A2").CurrentRegion.Value
ReDim y(1 To UBound(x, 1) * UBound(x, 2), 1 To UBound(x, 2))
For i = 2 To UBound(x, 1)
    For j = 1 To UBound(x, 2)
         If Len(x(i, j)) Then
            k = k + 1
           'y(k, 1) = x(i, 1)
           y(k, 1) = x(i, j)
        End If
    Next j
Next i

With Sheets.Add
    .UsedRange.ClearContents
    .Range("A2").Resize(k, UBound(x, 2)).Value = y()
    .Columns.AutoFit
    .Activate
End With
End Sub

Hi,

I haven't explained myself as I should. Your formula does transpose the values, but I'd like the result to be on 2 columns, as below:
sample.jpg

can it be done?

Thank you!

xlepws,
I am now confused. My first does what you wanted it. I really do not understand what you want.

Sub trans()
Dim x, y(), i&, j&, k&
x = Sheets("IPs").Range("A2").CurrentRegion.Value
ReDim y(1 To UBound(x, 1) * UBound(x, 2), 1 To UBound(x, 2))
For i = 2 To UBound(x, 1)
    For j = 2 To UBound(x, 2)
         If Len(x(i, j)) Then
            k = k + 1
           y(k, 1) = x(i, 1)
           y(k, 2) = x(i, j)
        End If
    Next j
Next i

With Sheets.Add
    .UsedRange.ClearContents
    .Range("A1").Resize(k, UBound(x, 2)).Value = y()
    .Columns.AutoFit
    .Activate
End With
End Sub

mmh..yet, I got something different applying the script. see attachment..unless I did something wrong.

xlepws,
I have changed the code to match the JPG you had attached in post #6. You now have changed the goal post again. I will pass on the button to next person who can help you out. Finito!

AB33, thanks a lot for your help.
Really, I had no intention of offending you If you read my first post I said my wanted output should be
A 1
A 2
..
A 5
B 1
B 2
..
B 100
C 1
C 2
..
C 299

So, repeating the label until there was a different value for that. But eventually, I'm not English so my explanation might have not been as clear as I thought.

Thanks again for your help!