How To Count Paired Data

[2blues]

Hey Guys,

Having trouble solving this one. I have data that is displayed like this:

England 4
France 3

Brazil 4
USA 4

USA 4
Canada 3

I'm trying to count how many times a combo of England,France occurred as a 4/3, Brazil, USA as a 4/4 and so on...the data is displayed vertically unfortunately. The numbers can vary (4/2, 5/3, 4,1 for ex) but it will always be a combo of 2 names. The two names and sets of numbers can occur multiple times or just once.

Thanks




<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[nemmi69]

Are the names in different rows?

 

[2blues]

Are the names in different rows?

Yes so it looks like this with cell numbers for example:

A1 B1
England 4
A2 B2
London 3

There is also an empty row after each pair, however if that is an issue I can always delete the empty rows so they follow one after another.

 

[nemmi69]

Try this

Dim Aloop As Integer
Dim LastRowNo As Integer
Dim Team1 As String
Dim Team2 As String
Dim Combo As Integer


Sub FindCombo()
LastRowNo = Range("A65536").End(xlUp).Row 'Just to set list of names
If LastRowNo <= 1 Then Exit Sub


' Just so the tea names can be changed
' else set Team1 = "England", Team2 = "France"
Team1 = Range("E2").Value
Team2 = Range("F2").Value
Combo = 0


For Aloop = 2 To LastRowNo Step 3
        If Range("A" & Aloop).Value = Team1 And Range("A" & Aloop + 1).Value = Team2 Then
            Combo = Combo + 1
        End If
Next Aloop


Range("G2").Value = Combo
End Sub

I put these in the rows and columns shown. ALternately you could enter the names directly as mentioned in the code.

E F G
1 Team 1 Team 2 Combination
2 England France