Vba to match 2 tables and count the number of occurances
Results 1 to 2 of 2

Thread: Vba to match 2 tables and count the number of occurances
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vba to match 2 tables and count the number of occurances

    Hi,
    Can someone help me with the vba for the following-
    I have 2 tables in different spreadsheets, which have a common field -policy no.
    The main table is in spreadsheet 1 and the other in spreadsheet 2.
    What is required is to find the number of times a policy no repeats itself in the second table.
    Eg - policy "a" repeats itself 4 times in the second table.

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,745
    Post Thanks / Like
    Mentioned
    66 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Vba to match 2 tables and count the number of occurances

    Welcome to the forum

    Try something like this
    - amend sheet names and ranges to match your own

    Code:
    Sub Repeats()
        Dim Policy As Range, One As Range, Two As Range, ws As Worksheet
        Set One = Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp))
        Set Two = Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp))
        
        Set ws = Sheets.Add(before:=One.Parent)
        ws.Range("A1:B1") = Array("Policy Number", "Count")
        
        For Each Policy In One
            ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2) = Array(Policy, WorksheetFunction.CountIf(Two, Policy))
        Next
    End Sub
    Results

    Excel 2016 (Windows) 32 bit
    A
    B
    1
    Policy Number Count
    2
    P001
    3
    3
    P002
    11
    4
    P003
    1
    5
    P004
    1
    6
    P005
    1
    7
    P006
    1
    8
    P007
    3
    9
    P008
    3
    10
    P009
    1
    11
    P010
    5
    Sheet: Results

    The 2 tables

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    1
    Policy number Name Date
    2
    P001 Laurel
    19/09/2018
    3
    P002 Hardy
    01/05/2018
    4
    P003 Bonnie
    29/04/2018
    5
    P004 Clyde
    17/06/2018
    6
    P005 Sciooby
    12/12/2018
    7
    P006 Shaggy
    20/07/2018
    8
    P007 Tom
    21/04/2018
    9
    P008 Jerry
    02/10/2018
    10
    P009 Batman
    27/01/2019
    11
    P010 Robin
    07/08/2018
    Sheet: Sheet1

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    1
    Policy number area type
    2
    P008 AO
    2
    3
    P009 AP
    1
    4
    P007 AU
    3
    5
    P002 AX
    3
    6
    P005 AY
    2
    7
    P001 BL
    2
    8
    P003 BO
    2
    9
    P010 BU
    1
    10
    P002 CM
    3
    11
    P006 CX
    1
    12
    P004 DS
    3
    13
    P001 DY
    2
    14
    P007 EO
    1
    15
    P002 EV
    2
    16
    P010 FM
    2
    17
    P008 FR
    3
    18
    P008 FW
    2
    19
    P002 HM
    3
    20
    P002 HM
    2
    21
    P001 HP
    1
    22
    P002 HY
    2
    23
    P002 IV
    1
    24
    P002 JQ
    2
    25
    P007 JQ
    1
    26
    P010 JQ
    1
    27
    P010 JV
    1
    28
    P002 KL
    2
    29
    P002 KN
    3
    30
    P002 KR
    2
    31
    P010 KX
    1
    Sheet: Sheet2

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •