Count and list paired combination from multiple list

[HorseRacing]

Hi
I have data as shown in table below.

Race	Tipster 1	Tipster 2	Tipster 3	Tipster 4	..	..	...	Tipster 12
1	Horsey	Table	Car	Phone
1	Table	Horsey	Horsey	Horsey
1	Chairman	car	Phone	Table
2
2..up to race 11

I want to list and count paired combination in multiple list (various Tipsters) for the first 2 horses they choose. Eg. of the 12 tipsters, how many chose Horsey and Table as their 1st 2 choice. In the given data answer should return 2.

	Horsey	Table	Car	Phone
Horsey	-	2
Table
Car	1
Phone	1

Please help.

If possible.....i would like to be extend to have paired combination that goes up to the 1st 3 combi. Meaning Horsey and Table appears as tips by Tipster 1, tipster 2 and Tipster 4...means this occurs 3 times. Even though it is not the 1st two choices by Tipster 4.

Thanks

 

[Eric W]

Welcome to the MrExcel forum!

This is probably better suited to a VBA solution, nevertheless, here's a formula version:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Horses		Pairings		Times chosen		Tipster 1	Tipster 2	Tipster 3	Tipster 4	Tipster 5	Tipster 6	Tipster 7	Tipster 8	Tipster 9	Tipster 10	Tipster 11	Tipster 12
2	Slowpoke		Slowpoke	Horsey	1		Horsey	Table	Car	Phone	DarkHorse	Horsey	Phone	Table	Horsey	BigWinner	BigWinner	BigWinner
3	Horsey		Slowpoke	BigWinner	2		Table	Horsey	Horsey	Horsey	Table	Chairman	Table	Phone	Slowpoke	Slowpoke	car	Horsey
4	BigWinner		Slowpoke	Chairman	0		Chairman	car	Phone	Table	Slowpoke	Phone	Chairman	Chairman	Table	car	Slowpoke	Phone
5	Chairman		Slowpoke	Table	2
6	Table		Slowpoke	Car	2
7	Car		Slowpoke	Phone	0
8	Phone		Slowpoke	DarkHorse	1
9	DarkHorse		Horsey	BigWinner	1
10			Horsey	Chairman	2
11			Horsey	Table	4
12			Horsey	Car	2
13			Horsey	Phone	4
14			Horsey	DarkHorse	0
15			BigWinner	Chairman	0
16			BigWinner	Table	0
17			BigWinner	Car	2
18			BigWinner	Phone	1
19			BigWinner	DarkHorse	0
20			Chairman	Table	3
21			Chairman	Car	0
22			Chairman	Phone	3
23			Chairman	DarkHorse	0
24			Table	Car	1
25			Table	Phone	3
26			Table	DarkHorse	1
27			Car	Phone	1
28			Car	DarkHorse	0
29			Phone	DarkHorse	0
30
Sheet11

Cell Formulas
Range	Formula
C2:C30	C2	=IF(ROWS($C$2:$C2)>COMBIN(COUNTA($A$2:$A$16),2),"",INDEX(A$2:A$16,LOOKUP(ROWS(C$2:C2)-1,COMBIN(COUNTA($A$2:$A$16),2)-COMBIN(COUNTA($A$2:$A$16)+1-ROW(INDIRECT("1:"&COUNTA($A$2:$A$16)-1)),2),ROW(INDIRECT("1:"&COUNTA($A$2:$A$16))))))
D2:D30	D2	=IF(C2="","",INDEX(INDEX(A$2:A$16,MATCH(C2,A$2:A$16,0)):A$16,COUNTIFS(C$2:C2,C2)+1))
E2:E30	E2	=IF(C2="","",SUMPRODUCT(--(COUNTIF(OFFSET($G$2:$G$4,0,COLUMN($G$2:$R$2)-COLUMN($G$2)),C2)+COUNTIF(OFFSET($G$2:$G$4,0,COLUMN($G$2:$R$2)-COLUMN($G$2)),D2)=2)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E:E	Expression	=E1=MAX(E:E)	text	NO

The C:D formulas are used to create a list of the pairings. You put the slate of horses in column A. If there are 8 horses in the race, there are 28 possible pairings. Then you'd put in the Tipster picks. Then the formula in column E counts how many times each pairing is found. I added some Conditional Formatting to highlight the pairing(s) that come up most.

Hope this helps!

 

[HorseRacing]

hi Eric.
Thank you for the solution.
is there anyway that column A can auto generate the choices made by the tipsters? Rather than me puttingall the ho

 

[Eric W]

That's one reason I said VBA might be better. Try this:

Open a new workbook and set up your worksheet like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Race	Tipster 1	Tipster 2	Tipster 3	Tipster 4	Tipster 5	Tipster 6	Tipster 7	Tipster 8	Tipster 9	Tipster 10	Tipster 11	Tipster 12
2	1	Horsey	Table	Car	Phone	DarkHorse	Horsey	Phone	Table	Horsey	BigWinner	BigWinner	BigWinner
3	1	Table	Horsey	Horsey	Horsey	Table	Chairman	Table	Phone	Slowpoke	Slowpoke	car	Horsey
4	1	Chairman	car	Phone	Table	Slowpoke	Phone	Bigleap	Chairman	Table	car	Slowpoke	Phone
5	2	Quickstep	BigLeap	Magic	PickMe	BigLeap	PickMe	Magic	BigLeap	FastPony	TooSlow	Magic
6	2	FastPony	Magic	Quickstep	Champ	Quickstep	Champ	TooSlow	Magic	TooSlow	PickMe	BigLeap
7	2	PickMe	Champ	BigLeap	TooSlow	Magic	Magic	Quickstep	PickMe	PickMe	BigLeap	FastPony
8	3	Rocket	Quasar	Asteroids	Comet	Comet	Meteor	Nova	Rocket	Asteroids	Asteroids	Star	Quasar
9	3	Star	Asteroids	Star	Meteor	Sputnik	Rocket	Rocket	Meteor	Quasar	Star	Rocket	Nova
Sheet11

Then Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Paste the following code into the sheet that opens:

Sub RankPairs()
Dim TopLeft As Range, r1 As Long, r2 As Long, r1a As Long, r1b As Long
Dim race As Long, c As Long, d1 As Object, k1 As String

    Set TopLeft = Range("A2")
    
    While TopLeft <> ""
        r1 = TopLeft.Row
        race = TopLeft.Value
        While TopLeft.Value = race
            Set TopLeft = TopLeft.Offset(1)
        Wend
        r2 = TopLeft.Row - 1
        Set d1 = CreateObject("Scripting.Dictionary")
        For c = 2 To 13
            For r1a = r1 To r2 - 1
                For r1b = r1a + 1 To r2
                    If Cells(r1a, c) <> "" And Cells(r1b, c) <> "" Then
                        If LCase(Cells(r1a, c).Value) < LCase(Cells(r1b, c).Value) Then
                            k1 = LCase(Cells(r1a, c).Value) & "/" & LCase(Cells(r1b, c).Value)
                        Else
                            k1 = LCase(Cells(r1b, c).Value) & "/" & LCase(Cells(r1a, c).Value)
                        End If
                        d1(k1) = d1(k1) + 1
                    End If
                Next r1b
            Next r1a
        Next c
        
        If d1.Count > 1 Then
            Cells(r1, "P").Resize(d1.Count) = WorksheetFunction.Transpose(d1.keys)
            Cells(r1, "Q").Resize(d1.Count) = WorksheetFunction.Transpose(d1.items)
            Range("P2:Q19").Select
            With ActiveSheet.Sort
                .SortFields.Clear
                .SetRange Range("P" & r1 & ":Q" & r1 + d1.Count - 1)
                .SortFields.Add2 Key:=Cells(r1, "Q"), Order:=xlDescending
                .Header = xlNo
                .Orientation = xlTopToBottom
                .Apply
            End With
            Cells(r2 + 1, "P").Resize(d1.Count, 2).ClearContents
        End If
    Wend
    
    Range("P2").Select
End Sub

Press Alt-Q to Quit the VBA editor. In Excel, press Alt-F8 to open the macro selector. Select RankPairs and click OK. You should end up with something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Race	Tipster 1	Tipster 2	Tipster 3	Tipster 4	Tipster 5	Tipster 6	Tipster 7	Tipster 8	Tipster 9	Tipster 10	Tipster 11	Tipster 12
2	1	Horsey	Table	Car	Phone	DarkHorse	Horsey	Phone	Table	Horsey	BigWinner	BigWinner	BigWinner			horsey/table	4
3	1	Table	Horsey	Horsey	Horsey	Table	Chairman	Table	Phone	Slowpoke	Slowpoke	car	Horsey			horsey/phone	4
4	1	Chairman	car	Phone	Table	Slowpoke	Phone	Bigleap	Chairman	Table	car	Slowpoke	Phone			phone/table	3
5	2	Quickstep	BigLeap	Magic	PickMe	BigLeap	PickMe	Magic	BigLeap	FastPony	TooSlow	Magic				bigleap/magic	5
6	2	FastPony	Magic	Quickstep	Champ	Quickstep	Champ	TooSlow	Magic	TooSlow	PickMe	BigLeap				magic/quickstep	3
7	2	PickMe	Champ	BigLeap	TooSlow	Magic	Magic	Quickstep	PickMe	PickMe	BigLeap	FastPony				pickme/tooslow	3
8	3	Rocket	Quasar	Asteroids	Comet	Comet	Meteor	Nova	Rocket	Asteroids	Asteroids	Star	Quasar			rocket/star	2
9	3	Star	Asteroids	Star	Meteor	Sputnik	Rocket	Rocket	Meteor	Quasar	Star	Rocket	Nova			asteroids/quasar	2
Sheet11