VBA Code or Formula to get all combinations of 2 cells on each row

[marshen]

Hi all.

I'm looking for some code or a formula that will solve the following issue that I have.

This is to work out all the combinations regarding the order of goals scored within a football match. Basically the H means Home Team and the A means Away team.

The requirement is to get all the possible combinations regarding the order the goals are scored.

So for
1-0 the only combination is H
2-0 the only combination is HH
2-1 the combinations are HHA, HAH, AHH
3-0 the only combination is HHH
3-1 the combinations are HHHA, HHAH, HAHH, AHHH

So the above gives an idea of what i'm looking for.

In the 3-1 example the results are broken down as follows

HHHA The home team scores the first 3 goals and then the away team scores the last goal of the match
HHAH The home team scores the first 2 goals of the match, the away team then scores 1 and then the home team scores the last goal of the match
HAHH The home teams scores the first goal of the match, the away team then score the 2nd goal of the match, then the home team go on and score the 3rd and the 4th goal of the match
AHHH The away team score the first goal of the match, then the home team score the next 3 goals

All the above combinations result in a 3-1 result, however the goals could be scored in any of the above orders.

Below are 3 screenshots showing the whole sheet. As you can see when the scores get to 9-8, there will be many possible combinations in regards to the order of the goals, hence the need for a macro or formula to calculate these.

In an ideal world, a formula / macro would populate each possible outcome starting in column D for the corresponding row, so for our 3-1 example and all the scores above, the first screenshot below shows the desired outcome, with all the combinations shown in red.

Below shows the full sheet.

I hope this all makes sense and isn't too difficult.

Thanks
Mark

 

[Eric W]

I tried to figure out a recursive LAMBDA function, but ended up writing it as a UDF. Open a new workbook, press Alt-F11 to open the VBA editor, press Alt-IM to insert a module, then paste this code:

Public Function SoccerOrders(home As Long, away As Long)
Dim result As Object

    Set result = CreateObject("Scripting.Dictionary")
    Call recur(home, away, "", result)
    SoccerOrders = result.keys
    
End Function

Sub recur(ByVal home As Long, ByVal away As Long, ByVal pattern As String, ByRef result As Object)
    
    If home = 0 And away = 0 Then result.Add pattern, 0
    If home > 0 Then Call recur(home - 1, away, pattern & "H", result)
    If away > 0 Then Call recur(home, away - 1, pattern & "A", result)
    
End Sub

Then use the function on the sheet as follows:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Home Score	Away Score		Patterns
2	0	0
3	1	0		H
4	0	1		A
5	2	0		HH
6	2	1		HHA	HAH	AHH
7	2	2		HHAA	HAHA	HAAH	AHHA	AHAH	AAHH
8	2	3		HHAAA	HAHAA	HAAHA	HAAAH	AHHAA	AHAHA	AHAAH	AAHHA	AAHAH	AAAHH
9	4	1		HHHHA	HHHAH	HHAHH	HAHHH	AHHHH
10
Sheet3

Cell Formulas
Range	Formula
D2:D5,D10,D9:H9,D8:M8,D7:I7,D6:F6	D2	=soccerorders(A2,B2)

I can rewrite this as a regular macro too.

 

[marshen]

I tried to figure out a recursive LAMBDA function, but ended up writing it as a UDF. Open a new workbook, press Alt-F11 to open the VBA editor, press Alt-IM to insert a module, then paste this code:

Public Function SoccerOrders(home As Long, away As Long)
Dim result As Object

    Set result = CreateObject("Scripting.Dictionary")
    Call recur(home, away, "", result)
    SoccerOrders = result.keys
   
End Function

Sub recur(ByVal home As Long, ByVal away As Long, ByVal pattern As String, ByRef result As Object)
   
    If home = 0 And away = 0 Then result.Add pattern, 0
    If home > 0 Then Call recur(home - 1, away, pattern & "H", result)
    If away > 0 Then Call recur(home, away - 1, pattern & "A", result)
   
End Sub

Then use the function on the sheet as follows:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Home Score	Away Score		Patterns
2	0	0
3	1	0		H
4	0	1		A
5	2	0		HH
6	2	1		HHA	HAH	AHH
7	2	2		HHAA	HAHA	HAAH	AHHA	AHAH	AAHH
8	2	3		HHAAA	HAHAA	HAAHA	HAAAH	AHHAA	AHAHA	AHAAH	AAHHA	AAHAH	AAAHH
9	4	1		HHHHA	HHHAH	HHAHH	HAHHH	AHHHH
10
Sheet3

Cell Formulas
Range	Formula
D2:D5,D10,D9:H9,D8:M8,D7:I7,D6:F6	D2	=soccerorders(A2,B2)

I can rewrite this as a regular macro too.

Morning Eric.

Thank you for this.
This looks to of worked exactly as I'd hoped, with 1 slight issue, but I don't think it really matters.

I'm getting the spill error on some rows that have 9, see below.
I'm guessing this is due to possibly not enough columns to return the results? I recall a Spill issue previously and it was something like that.

Any advice appreciated.

Thanks
Mark

 

[Eric W]

That's exactly right. A SPILL error occurs when there's an array of values, and there are not enough cells available in the row (or column) for them to all be displayed. You must have some data in the column that's 9 columns over preventing the rest of the results from being displayed. You can either move that data somewhere else, or if you can think of a different way to display it, let me know and I'll see if I can adapt the function.