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

 

[footoo]

Here's one way :

Sub v()
Dim rng As Range, i%
Set rng = Range([A1], [A2].End(4))
For i = 1 To rng.Count Step 4
   Cells(Rows.Count, "C").End(3)(2).Resize(, 4).Value = Application.Transpose(rng(i).Resize(4))
Next
End Sub

 

[footoo]

Set rng = Range([A1], [A2].End(4))

Should be :

Set rng = Range([A2], [A2].End(4))

 

[Dreamteam]

Hi Footoo,

Many thanks for this.

This is definitely good enough for what I want to do.

:~)

 

[Bo_Ry]

Please try at C2

=IF(COUNTIF(C$1,"*goals"),--MID(INDEX($A:$A,ROWS(C$2:C2)*4-1),COUNTIF($C$1:C$1,"*goals")*4-3,2),INDEX($A:$A,(ROWS(C$2:C2)-1)*4+COLUMNS($C2:C2)-(COLUMNS($C2:C2)>3)))

 

[Dreamteam]

Hi Bo_Ry

Sorry I missed this - um, no this does not do what I was hoping for...

Dt

 

[Dreamteam]

I hope I am allowed to do this... I think so...

The solution above is not really what I was hoping for - as I still need to split out the scores into Home and Away...

Does anyone else have any other solutions. As I mentioned at the beginning - I was assuming this could be stored in an array and then called upon to split out into the required columns. My brain says this should be straight forward but maybe not...

Any insights would be much apprecitated

dt

 

[Bo_Ry]

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

Cell Formulas
Range	Formula
C2:G6	C2	=IF(COUNTIF(C$1,"*goals"),--MID(INDEX($A:$A,ROWS(C$2:C2)*4-1),COUNTIF($C$1:C$1,"*goals")*4-3,2),INDEX($A:$A,(ROWS(C$2:C2)-1)*4+COLUMNS($C2:C2)-(COLUMNS($C2:C2)>3)))

If this is not what you want, then I have no idea at all.

 

[Dreamteam]

Bo_Ry...

This is perfect...!

I'm not quite sure what I did when it did not work.

Again... thanks for your time and your solution

Dt

 

[footoo]

Sub v()
Dim rng As Range, i%
Set rng = Range([A1], [A2].End(4))
For i = 1 To rng.Count Step 4
    Cells(Rows.Count, "C").End(3)(2).Resize(, 3).Value = Application.Transpose(rng(i).Resize(3))
    Cells(Rows.Count, "G").End(3)(2).Value = rng(i + 3)
Next
Range("E2:E" & Cells(Rows.Count, "E").End(3).Row).TextToColumns Destination:=Range("E2"), DataType:=xlDelimited, _
    Other:=True, OtherChar:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1))
End Sub