Need Help for Autofill

[mu4you]

My Data as follows:

In A1 - "Simens Limited"
In A2 - 04-1783-933 (entered as text)
In A3 - 04-7873-321 (text)

In A4 - Sisco Limited
In A5 - 05-898-aa-88 (entered as text)
In A6 - 05-883-bb-99 (entered as text)

Now, I need output as follows:

In A1 - Simens Limited and in B1 04-1783-933
In A2 - Simens Limited and in B2 04-7873-321

In A3 - Sisco Limited and in A3 05-898-aa-88
In A4 - Sisco Limited and in A4 05-883-bb-99

I really appreciate if anybody can help because this is large data.

Thanks in advance and waiting for your reply.

 

[JackDanIce]

Is all your data in column A and always in the pattern of 3's like below (e.g. header, then text, then text)?

If so, maybe this code can help:

Sub MyPenguinsWillConquerAll ()
 
Dim i as Long, j as Long
 
With Application
  .Calculation = xlCalculationManual
  .ScreenUpdating = False
End With
 
j = 1
 
For i = 1 to Range("A" & Rows.Count).End(xlUp).Row Step 3
  Range("B" & j) = Range("A" & i)
  Range("B" & j + 1) = Range("A" & i)
  Range("C" & j) = Range("A" & i + 1)
  Range("C" & j + 1) = Range("A" & i + 2)
  j = j + 2
Next i
 
Columns("A:A").Delete Shift:=xlToLeft
 
With Application
  .Calculation = xlCalculationAutomatic
  .ScreenUpdating = True
End With
 
End Sub

 

[wigi]

For instance:

Sub ff()
    Dim lCounter As Long
    lCounter = 1
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 3
        Range("B" & lCounter).Resize(2) = Range("A" & i).Value
        Range("C" & lCounter).Resize(2) = Range("A" & i + 1).Resize(2).Value
        lCounter = lCounter + 2
    Next
    Columns(1).Delete
End Sub]