Forum Discussion
Solved
Concatenating two lists of numbers of different lengths
I am attempting to concatenate two lists of numbers of differing lengths. Unfortunately, I am extremely new to this, so any help provided would be greatly appreciated.
In one list I have from 001 to 365 and in the other list I have 1 to 10. For each number from 001 to 365, I need them to have 10 iterations. I hope the image below explains it better.
How can this be accomplished in VBA?
If you prefer VBA:
Sub Expand() Dim r As Long Dim m As Long Dim s As Long Dim n As Long Dim t As Long Dim u Dim w Application.ScreenUpdating = False m = Range("U1").End(xlDown).Row u = Range("U1:U" & m).Value n = Range("W2").End(xlDown).Row w = Range("W2:W" & n).Value Range("O2").Resize(m * (n - 1)).NumberFormat = "@" t = 1 For r = 1 To m For s = 1 To n - 1 t = t + 1 Range("O" & t).Value = u(r, 1) & w(s, 1) Next s Next r Application.ScreenUpdating = True End Sub
Thank you for both of the replies, but HansVogelaar had the solution I was looking for.
If you prefer VBA:
Sub Expand() Dim r As Long Dim m As Long Dim s As Long Dim n As Long Dim t As Long Dim u Dim w Application.ScreenUpdating = False m = Range("U1").End(xlDown).Row u = Range("U1:U" & m).Value n = Range("W2").End(xlDown).Row w = Range("W2:W" & n).Value Range("O2").Resize(m * (n - 1)).NumberFormat = "@" t = 1 For r = 1 To m For s = 1 To n - 1 t = t + 1 Range("O" & t).Value = u(r, 1) & w(s, 1) Next s Next r Application.ScreenUpdating = True End SubTry the following formula:
=TOCOL( TEXT( A1:A3, "000" ) & TRANSPOSE(TEXT( C1:C3, "00" )))so the A1:A3 is your first list and then the C1:C3 is the second list. The "000" and "00" indicate how many digits to force the numbers to (i.e. make 1 show as 01).
