[VBA] Calculate how often does 2 person appear at same time

[smallxyz]

Attached below is a sample data.
purpose is to create a table (shown below) which summarizes how often do 2 persons occur at same time.

E.g.
Use "Rebecca" as an e.g..
Total count of "Rebeceaa" occurence in all invoices = 4.
For invoice # 4, "Wendy" & "Mary" also exist at same time w "Rebecca".
So their occurrence frequency relative to "Rebecca" would be 1/4 (25%) & 1/4 (25%) respectively.

I would to automate above process ( fill-up whole table ) via vba or any other way convenient & fast.
I get a large data set, >1000 data row. Manual calculation would be deadly.
Hope someone can help.



A sample workbook is attached.
Thanks!

 

[Chihiro]

Can you elaborate on this part?

For invoice # 4, "Wendy" & "Mary" also exist at same time w "Rebecca".
So their occurrence frequency relative to "Rebecca" would be 1/4 (25%) & 1/4 (25%) respectively.

There's 3 names found on invoice #4. Should it not be 1/3 for each, or 33.3%?

For count of each name... You can do something like below. Or use formula (do a search in forum and you will find plenty of examples).

Sub CountUnique()
Dim myArr
Dim i As Long

With Sheets("Worksheet")
    myArr = .Range("B2:B" & .Cells(Rows.Count, "B").End(xlUp).Row).Value
End With

With CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(myArr)
        If .Exists(myArr(i, 1)) Then
            .Item(myArr(i, 1)) = .Item(myArr(i, 1)) + 1
        Else
            .Item(myArr(i, 1)) = 1
        End If
    Next
    Sheets("Worksheet").[D2].Resize(.Count) = Application.Transpose(.Keys)
    Sheets("Worksheet").[E2].Resize(.Count) = Application.Transpose(.Items)
End With
End Sub

Another method is to just copy the name to another sheet, remove duplicates. Then use COUNTIF.

 

[Chihiro]

Ah, I misread. So, since Rebecca occurs in 4 invoice, you want to see in how many of that invoice May/Wendy occurs. They each appear in 1 invoice so 1/4.

Hmm, let me think on it a bit.

Edit: Another question, what should be the result for Tom & Betty?

Since Betty appears twice in same invoice... how should that be treated?

 

[smallxyz]

For Data like
10 Tom
10 Betty
10 Betty

We regard it as
10 Tom
10 Betty

since the purpose is to see how often each pair occurs at the same time.

So for (Tom,Betty) pair , it would be 1/2 = 50%

 

[Chihiro]

Then what does total count represent?

For Betty, you have 4, but should it not be 2? (There are 3 records, but 2 of it belong to same invoice).
For Tom, you have 1, but should it not be 2? (2 separate invoice)

Can you update sample file to accurately represent what you want as end result?

 

[smallxyz]

Chihiro, first of all, sorry for occupying your time so much. The file is full of amended formula. So the digit does not appear as it ought to be. Your doubts are right.

After re-thinking through your questions, I see many flaws in my current methodology. I guess I will re-design a way to come up with a better calculation.

Thank you for your time!