Count how many times a postcode appears

[mason001]

Hi there, I've been given a spread and asked to work out how many times the same postcodes appear within the spread.

For example... If I was given the postcodes below, I'd like to have a function to count of how many times they appear... so RH140HW is on there 3 times, RH204NU 2 times, etc.; so I know what is the postcode that appears most-to-least. I've got over 8500 postcodes to sort through and count!

[TABLE="width: 89"]
<tbody>[TR]
[TD]PO7 6PR
RH204NU
RH140HW
RH140HW
RH138BE
BN435NG
PO200DP
RH204NU
RH140HW
RH138BE
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Any help is much appreciated.

Thanks[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[jorismoerings]

Hi,

Try this formula. It will determine if the postcode on the row appears for the first time in the list and if so count how many times occurs in the complete list.

Book1
	A	B
1	code	Unique Count
2	PO7 6PR	1
3	RH204NU	2
4	RH140HW	3
5	RH140HW
6	RH138BE	2
7	BN435NG	1
8	PO200DP	1
9	RH204NU
10	RH140HW
11	RH138BE
Sheet1

Cell Formulas
Range	Formula
B2	=IF(COUNTIF($A$2:A2,A2)=1,COUNTIF($A$2:$A$11,A2),"")

 

[FormR]

I've got over 8500 postcodes to sort through and count!

Hi, welcome to the forum!

Another option would be to use a pivot table, you would drag the post code field to both the "rows area" and the "values area".

 

[My Aswer Is This]

I would use this vba code:

Assuming your list of postal codes are in Column "A"
The unique postal codes will be put in column "B"
And the number of times each postal code is found will be put in column "C" next to the unique postal code.

Sub Count_Me()
'Modified 12-6-17 6:05 AM EST
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim Lastrowb As Long
Range(Cells(1, 1), Cells(Lastrow, 1)).Copy Cells(1, 2)
Lastrowb = Cells(Rows.Count, "B").End(xlUp).Row
Range(Cells(1, 2), Cells(Lastrowb, 2)).RemoveDuplicates 1, xlNo
Lastrowb = Cells(Rows.Count, "B").End(xlUp).Row
    For i = 1 To Lastrowb
        Cells(i, "C").Value = WorksheetFunction.CountIf(Range("A1:A" & Lastrow), Cells(i, 2).Value)
    Next
Application.ScreenUpdating = True
End Sub

 

[mason001]

Hi, welcome to the forum!

Another option would be to use a pivot table, you would drag the post code field to both the "rows area" and the "values area".

This has worked! Thank you very much!