Impossible or Not

[HappyChappy]

Hi I have a problem which I don't think is possible in excel but thought I'd throw it out there as this site has some amazing members who have wowed me in the past.
I have two sheets one has data which i want pulled onto the 2nd sheet but in a very particular way. the cell has two numbers in it example 15 & 16 i need to pull the route number, Name and Registration onto sheet two but split the two routes.

Sheet 1 C1 =15 & 16 D1= Mr Smith F1= GH56TZH
C2= 17 D2 = Mr Jones F2 FD12YHJ

Sheet 2 C1= 15 D1= Mr Smith F1= GH56TZH
C2= 16 D2= Mr Smith F2= GH56TZH
C3= 17 C3= Mr Jones F3= FD12YHJ

Can a cell with two number be split in this way? Ps not all c1 cells have double numbers some are standard single ones

Regards

 

[Peter_SSs]

1. Is two the most numbers in a single cell?
2. If more are possible are they all separated by space then ampersand then space?
3. About how many rows are likely in the original data?
4. About how many rows are likely in the final result?
5. If it turns out a macro is the best way is that acceptable?
6. What version of Excel are you using?


Edit:
7. Are all the numbers 2 digits?

 

[HappyChappy]

Two numbers are the most entered separated by & maximum are 35 routes
Excel 2016 hope this helps

 

[Peter_SSs]

Two numbers are the most entered separated by & maximum are 35 routes
Excel 2016 hope this helps

It seems to help with 1, 2, 3, 4 & 6. That leaves 5 & 7.

 

[HappyChappy]

So numbers 1 to 35 will be used but some in pairs ie: 4&5 as two routes for this vehicle on the day.
never thought it could be done was a shot in the dark. you say it needs to be in a macro to achieve the result.
will await the necessary code and thank you for the assistance.

 

[Peter_SSs]

you say it needs to be in a macro to achieve the result.

I didn't say that before. I was trying to collect relevant information to decide. Now I am suggesting that is how I would do it.

Give this a try in a copy of your workbook after checking the sheet names for accuracy.

Sub Get_Routes()
  Dim a As Variant, b As Variant, RouteNo As Variant
  Dim i As Long, lr As Long, k As Long
  
  With Sheets("Sheet 1")
    lr = .Range("C" & .Rows.Count).End(xlUp).Row
    a = Application.Index(.Cells, Evaluate("row(1:" & lr & ")"), Array(3, 4, 6))
  End With
  ReDim b(1 To 1000, 1 To 3)
  For i = 1 To UBound(a)
    For Each RouteNo In Split(Replace(a(i, 1), " ", ""), "&")
      k = k + 1
      b(k, 1) = RouteNo
      b(k, 2) = a(i, 2)
      b(k, 3) = a(i, 3)
    Next RouteNo
  Next i
  With Sheets("Sheet 2")
    .Range("C1:D1").Resize(k).Value = Application.Index(b, Evaluate("row(1:" & k & ")"), Array(1, 2))
    .Range("F1").Resize(k).Value = Application.Index(b, Evaluate("row(1:" & k & ")"), 3)
  End With
End Sub

 

[HappyChappy]

Hi Peter, just tried out the macro definitely on the right track, I needed to be a bit clearer on my needs
sheet 2 A4 is the start of data for my numbers c4 is for the name and j4 is for the Registration
Sheet1 c3:c44 is the number d3:d44 the name and m3:m44 is the Registration
it is amazing you are able to break it down never thought it was going to feezable

 

[Peter_SSs]

See if this is better

Sub Get_Routes_v2()
  Dim a As Variant, b As Variant, RouteNo As Variant
  Dim i As Long, lr As Long, k As Long
  
  With Sheets("Sheet 1")
    lr = .Range("C" & .Rows.Count).End(xlUp).Row
    a = Application.Index(.Cells, Evaluate("row(3:" & lr & ")"), Array(3, 4, 13))
  End With
  ReDim b(1 To 1000, 1 To 3)
  For i = 1 To UBound(a)
    For Each RouteNo In Split(Replace(a(i, 1), " ", ""), "&")
      k = k + 1
      b(k, 1) = RouteNo
      b(k, 2) = a(i, 2)
      b(k, 3) = a(i, 3)
    Next RouteNo
  Next i
  With Sheets("Sheet 2")
    .Range("A4").Resize(k).Value = Application.Index(b, Evaluate("row(1:" & k & ")"), 1)
    .Range("C4").Resize(k).Value = Application.Index(b, Evaluate("row(1:" & k & ")"), 2)
    .Range("J4").Resize(k).Value = Application.Index(b, Evaluate("row(1:" & k & ")"), 3)
  End With
End Sub

 

[HappyChappy]

You sir are a legend!!!!!

 

[Peter_SSs]

Glad it helped. Thanks for the follow-up.