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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Policy Number[/td][td]Count[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]P001[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]P002[/td][td]

11​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]P003[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]P004[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]P005[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]P006[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]P007[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]P008[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]P009[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]P010[/td][td]

5​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Results[/td][/tr][/table]

The 2 tables

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#4472C4]Policy number[/td][td=bgcolor:#4472C4]Name[/td][td=bgcolor:#4472C4]Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#D9E1F2]P001[/td][td=bgcolor:#D9E1F2]Laurel[/td][td=bgcolor:#D9E1F2]

19/09/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]P002[/td][td]Hardy[/td][td]

01/05/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#D9E1F2]P003[/td][td=bgcolor:#D9E1F2]Bonnie[/td][td=bgcolor:#D9E1F2]

29/04/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]P004[/td][td]Clyde[/td][td]

17/06/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td=bgcolor:#D9E1F2]P005[/td][td=bgcolor:#D9E1F2]Sciooby[/td][td=bgcolor:#D9E1F2]

12/12/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]P006[/td][td]Shaggy[/td][td]

20/07/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td=bgcolor:#D9E1F2]P007[/td][td=bgcolor:#D9E1F2]Tom[/td][td=bgcolor:#D9E1F2]

21/04/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]P008[/td][td]Jerry[/td][td]

02/10/2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td=bgcolor:#D9E1F2]P009[/td][td=bgcolor:#D9E1F2]Batman[/td][td=bgcolor:#D9E1F2]

27/01/2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]P010[/td][td]Robin[/td][td]

07/08/2018​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#4472C4]Policy number[/td][td=bgcolor:#4472C4]area[/td][td=bgcolor:#4472C4]type[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#D9E1F2]P008[/td][td=bgcolor:#D9E1F2]AO[/td][td=bgcolor:#D9E1F2]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]P009[/td][td]AP[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#D9E1F2]P007[/td][td=bgcolor:#D9E1F2]AU[/td][td=bgcolor:#D9E1F2]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]P002[/td][td]AX[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td=bgcolor:#D9E1F2]P005[/td][td=bgcolor:#D9E1F2]AY[/td][td=bgcolor:#D9E1F2]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]P001[/td][td]BL[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td=bgcolor:#D9E1F2]P003[/td][td=bgcolor:#D9E1F2]BO[/td][td=bgcolor:#D9E1F2]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]P010[/td][td]BU[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td=bgcolor:#D9E1F2]P002[/td][td=bgcolor:#D9E1F2]CM[/td][td=bgcolor:#D9E1F2]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]P006[/td][td]CX[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td=bgcolor:#D9E1F2]P004[/td][td=bgcolor:#D9E1F2]DS[/td][td=bgcolor:#D9E1F2]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]P001[/td][td]DY[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td=bgcolor:#D9E1F2]P007[/td][td=bgcolor:#D9E1F2]EO[/td][td=bgcolor:#D9E1F2]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]P002[/td][td]EV[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td=bgcolor:#D9E1F2]P010[/td][td=bgcolor:#D9E1F2]FM[/td][td=bgcolor:#D9E1F2]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]P008[/td][td]FR[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td=bgcolor:#D9E1F2]P008[/td][td=bgcolor:#D9E1F2]FW[/td][td=bgcolor:#D9E1F2]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td]P002[/td][td]HM[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td=bgcolor:#D9E1F2]P002[/td][td=bgcolor:#D9E1F2]HM[/td][td=bgcolor:#D9E1F2]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

[/td][td]P001[/td][td]HP[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22​

[/td][td=bgcolor:#D9E1F2]P002[/td][td=bgcolor:#D9E1F2]HY[/td][td=bgcolor:#D9E1F2]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

23​

[/td][td]P002[/td][td]IV[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

24​

[/td][td=bgcolor:#D9E1F2]P002[/td][td=bgcolor:#D9E1F2]JQ[/td][td=bgcolor:#D9E1F2]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

25​

[/td][td]P007[/td][td]JQ[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

26​

[/td][td=bgcolor:#D9E1F2]P010[/td][td=bgcolor:#D9E1F2]JQ[/td][td=bgcolor:#D9E1F2]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

27​

[/td][td]P010[/td][td]JV[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

28​

[/td][td=bgcolor:#D9E1F2]P002[/td][td=bgcolor:#D9E1F2]KL[/td][td=bgcolor:#D9E1F2]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

29​

[/td][td]P002[/td][td]KN[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

30​

[/td][td=bgcolor:#D9E1F2]P002[/td][td=bgcolor:#D9E1F2]KR[/td][td=bgcolor:#D9E1F2]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

31​

[/td][td]P010[/td][td]KX[/td][td]

1​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]