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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,336
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,203
Messages
5,485,332
Members
407,497
Latest member
astaha

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top