Vba to match 2 tables and count the number of occurances

Gkriti

New Member
Joined
Aug 24, 2019
Messages
1
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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,249
Office Version
365
Platform
Windows
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 NumberCount
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 numberNameDate
2
P001Laurel
19/09/2018​
3
P002Hardy
01/05/2018​
4
P003Bonnie
29/04/2018​
5
P004Clyde
17/06/2018​
6
P005Sciooby
12/12/2018​
7
P006Shaggy
20/07/2018​
8
P007Tom
21/04/2018​
9
P008Jerry
02/10/2018​
10
P009Batman
27/01/2019​
11
P010Robin
07/08/2018​
Sheet: Sheet1

Excel 2016 (Windows) 32 bit
A
B
C
1
Policy numberareatype
2
P008AO
2​
3
P009AP
1​
4
P007AU
3​
5
P002AX
3​
6
P005AY
2​
7
P001BL
2​
8
P003BO
2​
9
P010BU
1​
10
P002CM
3​
11
P006CX
1​
12
P004DS
3​
13
P001DY
2​
14
P007EO
1​
15
P002EV
2​
16
P010FM
2​
17
P008FR
3​
18
P008FW
2​
19
P002HM
3​
20
P002HM
2​
21
P001HP
1​
22
P002HY
2​
23
P002IV
1​
24
P002JQ
2​
25
P007JQ
1​
26
P010JQ
1​
27
P010JV
1​
28
P002KL
2​
29
P002KN
3​
30
P002KR
2​
31
P010KX
1​
Sheet: Sheet2
 

Forum statistics

Threads
1,089,191
Messages
5,406,724
Members
403,104
Latest member
mkirchner

This Week's Hot Topics

Top