1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Discussion in 'VBA Macros' started by smallxyz, Aug 29, 2017.

  1. smallxyz

    smallxyz New Member

    Messages:
    20
    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.

    S.jpg

    A sample workbook is attached.
    Thanks!

    Attached Files:

    Last edited: Aug 29, 2017
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,805
    Can you elaborate on this part?
    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).
    Code (vb):
    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.
  3. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,805
    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?
  4. smallxyz

    smallxyz New Member

    Messages:
    20
    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%
  5. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,805
    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?
  6. smallxyz

    smallxyz New Member

    Messages:
    20
    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!
    Chihiro and NARAYANK991 like this.

Share This Page