A simple macro for a simple Array...?

[Dreamteam]

Hi all

Below is a list of data in col A of football results. The data pattern is consistent - Date of match; Home team; Score (home team first) and finally Away team. And then it moves onto the next result. I have been trying to simply move it over into a more readable format - starting with cell C2. I thought this would be easy but I am cannot seem to do this. I thought initially that I would put col A into an array and then move it to the table - but to no avail.

Any help would be much apprecitated.

Many thanks

Dt

Football - probabilities.xlsm
	A	B	C	D	E	F	G
2	19.05. 16:00		Date	Home Club	Home Goals	Away Goals	Away Club
3	Chelsea		19.05. 16:00	Chelsea	2	1	Everton
4	2 - 1		19.05. 16:00	Liverpool	1	0	QPR
5	Everton		19.05. 16:00	Manchester City	2	3	Norwich
6	19.05. 16:00		19.05. 16:00	Newcastle	0	1	Arsenal
7	Liverpool		19.05. 16:00	Southampton	1	1	Stoke
8	1 - 0
9	QPR
10	19.05. 16:00
11	Manchester City
12	2 - 3
13	Norwich
14	19.05. 16:00
15	Newcastle
16	0 - 1
17	Arsenal
18	19.05. 16:00
19	Southampton
20	1 - 1
21	Stoke
Sheet5

 

[mikerickson]

I think this will do what you want. I notice that your example data started in A2 and the results started in C3

Sub test()
    Dim arrInput As Variant
    Dim arrOutput As Variant
    Dim rngOutput As Range
    Dim i As Long, Pointer As Long
    Set rngOutput = Range("C3")
    
    With Range("A:A")
        With Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
            ReDim arrOutput(1 To .Rows.Count / 4, 1 To 5)
            arrInput = .Value
        End With
    End With
    
    For i = 1 To UBound(arrInput, 1) Step 4
        Pointer = Pointer + 1
        arrOutput(Pointer, 1) = arrInput(i, 1)
        arrOutput(Pointer, 2) = arrInput(i + 1, 1)
        arrOutput(Pointer, 3) = Trim(Split(arrInput(i + 2, 1), "-")(0))
        arrOutput(Pointer, 4) = Trim(Split(arrInput(i + 2, 1) & "-", "-")(1))
        arrOutput(Pointer, 5) = arrInput(i + 3, 1)
    Next i
    
    rngOutput.Resize(Pointer, 5).Value = arrOutput
End Sub

 

[Dreamteam]

Footoo, Bo_Ry and now Mike - thankyou all!

All of the solutions that you have given all work!

Thank you all for your time (and patience!)

Dt