Count Postcodes within a sheet

[TryingtoLearnExcel4]

Hi

I’ve just registered on this site so apologises in advance if my post is incorrect in any way.

I need some help :

I have a list of postcodes for a particular county (wanted list)
I have another list of random postcodes from lots of different counties (general list)

I need to identify how many times postcodes from my wanted list occur in the general list.

The general list is approx 80000 records.

I’ve been reading around and pivot tables are mentioned a lot.

I’ve got very basic excel experience and don’t know much even about pivot tables. Can someone please please please walk me through this process I need to achieve my desired result?

Thanks in advance.

 

[Logit]

.
Paste this in a Routine Module :

Option Explicit


Sub IsItThere()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, c As Range, fn As Range
Dim i As Integer


Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
i = 0




lr = sh1.Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In Range("A2:A" & lr)
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                i = i + 1
                sh1.Range("D1").Value = i
            End If
    Next
End Sub

Download sample workbook : https://www.amazon.com/clouddrive/share/rcIQRQF8st41YNM7rtwmgmFRnt4uwMiHVcxKMZZpV6N

If you have your postal codes in Col A of Sheet 1 (Wanted List) and your General List in Col A of Sheet 2, the macro will display the total number of matches.

 

[TryingtoLearnExcel4]

Wow thanks a massive thank you for replying so quickly!

I’m not so clued on about how to do the routine module stuff but I will try it now and let you know how I get on within next couple of hours

 

[TryingtoLearnExcel4]

Hi @Logit

Thank you so much again - I've just checked it and it runs like a beauty! So grateful to you!

 

[TryingtoLearnExcel4]

@Logit

Or anyone else... is there a COUNTIF function or anything that could return results quicker? I’m searching approx 80000 records and this code is taking a while lol

 

[Logit]

.

=SUMPRODUCT(COUNTIF(Sheet1!A2:A30,Sheet2!A2:A30))

 

[TryingtoLearnExcel4]

Perfect thx !

 

[Logit]

.
You are welcome.