Vba to match 2 tables and count the number of occurances

[Gkriti]

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.

 

[Yongle]

Welcome to the forum

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

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