Counting (unknown at present) discrete values

F

F

I have a column that will hold postcodes from responses to a questionnaire.

I do not know which postcodes will appear in the list, but I would like
to count how many instances there are of each discrete postcode that
appears.

For example, the following are entered:
postcode1
postcode2
postcode1
postcode3
postcode2
postcode1

and I would want to the list translated into the form:
postcode1 - 3
postcode2 - 2
postcode3 - 1

Is there a (relatively) simple way to achieve this?

TIA
 
J

joeu2004

I have a column that will hold postcodes from responses
to a questionnaire. [....]
I would like to count how many instances there are of
each discrete postcode that appears. [....]
Is there a (relatively) simple way to achieve this?
Click to expand...

Define "relatively simple". For me, it's a macro. Granted, figuring
out the various machinations to make it happen can be a challenge.
But once that's done, perhaps it is "relatively simple" for you to
copy-and-paste the macro below into a VBA module, make the necessary
changes (see "Set src" and "Set dst"), then run the macro.

By the way, it is surprisingly fast. I tested it with a column of
65536 random postal codes, 20 unique ones. The macro completed in
about 0.9 sec on my 6-year-old computer (read: ancient!). YMMV.

Note: The macro is intended to handle postal codes that can be
entered as a number (e.g. 12345) intermixed with postal codes that
Excel interprets as text by default (e.g. 12345-1234). It should work
with numeric postal codes formatted as Custom 00000-0000 as well. But
I did not test that.

The macro....

Sub doit()
Dim oldCalc, p0, src As Range, dst As Range
Dim n As Long, i As Long, j As Long

'***** modify these *****
Set src = Range("b1") 'cell with first postal code
Set dst = Range("e1") 'target cell for list, 2 columns

oldCalc = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'src = column of contiguous cells
Set src = Range(src, src.End(xlDown))
n = src.Count

'convert all to text for sort so that
'12345-1234 follows 12345, for example
'(Text To Columns, format as Text)
src.TextToColumns Destination:=trg, DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 2), TrailingMinusNumbers:=False

'sort in text order
dst.Resize(n).Sort Key1:=trg, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'create list of discrete postal codes
'count duplicates
p0 = dst.Resize(n)
dst.Resize(n, 2).Clear
ReDim p(1 To n, 1 To 2)
p(1, 1) = p0(1, 1): p(1, 2) = 1
j = 1
For i = 2 To n
If p0(i, 1) = p(j, 1) Then p(j, 2) = p(j, 2) + 1 _
Else j = j + 1: p(j, 1) = p0(i, 1): p(j, 2) = 1
Next
dst.Resize(j, 2) = p

Application.Calculation = oldCalc
Application.ScreenUpdating = True
End Sub
 
J

joeu2004

Pivot Table would be useful.
Click to expand...

Probably the best idea. But it might be helpful to explain how. I
struggled with it myself, having never used pivot tables and finding
Walkenbach's "explanation" opaque as usual.

If the data are in B2:B65536 with a title in B1, click on Data > Pivot
Table.

1. In the first menu, be sure that "Where is the data?" is set to MS
Office Excel, and "What kind of report?" is set to Pivot Table.

2. In the next menu, select the range of the data, B:B or B1:B65536 in
my example. Note that B1 (the title) must be included in the range

3. In the next menu, select "Where do you want the report?".

4. Click Layout. Drag the button (with the title from B1) to the Data
area (becomes "count of title"). And drag the same button to the Row
area. Then click on OK.

5. Click Finish. Voila!

Detail.... In my case, the data is a mix of numerics (5-digit zip
codes) and text (5-hyphen-4-digit zip code). I don't like the way
that sorts. So I prefer to use Text To Column to convert everything
to text.

Apparently the Pivot Table does not track that change. So it must be
done __before__ creating the pivot table.
 
F

F

I have a column that will hold postcodes from responses
to a questionnaire. [....]
I would like to count how many instances there are of
each discrete postcode that appears. [....]
Is there a (relatively) simple way to achieve this?
Click to expand...

Define "relatively simple". For me, it's a macro. Granted, figuring
out the various machinations to make it happen can be a challenge.
But once that's done, perhaps it is "relatively simple" for you to
copy-and-paste the macro below into a VBA module, make the necessary
changes (see "Set src" and "Set dst"), then run the macro.

By the way, it is surprisingly fast. I tested it with a column of
65536 random postal codes, 20 unique ones. The macro completed in
about 0.9 sec on my 6-year-old computer (read: ancient!). YMMV.

Note: The macro is intended to handle postal codes that can be
entered as a number (e.g. 12345) intermixed with postal codes that
Excel interprets as text by default (e.g. 12345-1234). It should work
with numeric postal codes formatted as Custom 00000-0000 as well. But
I did not test that.

The macro....

Sub doit()
Dim oldCalc, p0, src As Range, dst As Range
Dim n As Long, i As Long, j As Long

'***** modify these *****
Set src = Range("b1") 'cell with first postal code
Set dst = Range("e1") 'target cell for list, 2 columns

oldCalc = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'src = column of contiguous cells
Set src = Range(src, src.End(xlDown))
n = src.Count

'convert all to text for sort so that
'12345-1234 follows 12345, for example
'(Text To Columns, format as Text)
src.TextToColumns Destination:=trg, DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 2), TrailingMinusNumbers:=False

'sort in text order
dst.Resize(n).Sort Key1:=trg, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'create list of discrete postal codes
'count duplicates
p0 = dst.Resize(n)
dst.Resize(n, 2).Clear
ReDim p(1 To n, 1 To 2)
p(1, 1) = p0(1, 1): p(1, 2) = 1
j = 1
For i = 2 To n
If p0(i, 1) = p(j, 1) Then p(j, 2) = p(j, 2) + 1 _
Else j = j + 1: p(j, 1) = p0(i, 1): p(j, 2) = 1
Next
dst.Resize(j, 2) = p

Application.Calculation = oldCalc
Application.ScreenUpdating = True
End Sub
Click to expand...

Amazing! Thank you!

I should have some 'real' data (UK postcodes in the format AB12 3CD)
later today so look forward to giving it some exercise...
 
J

joeu2004

trg ? dst, i believe.
Click to expand...

And dst.Resize(n).Sort Key1:=trg as well.

Good catch! I made a last-minute name change in my posting; guess I
missed a couple :-(. Usually I test such changes. Busted!

No matter. Gord's suggestion to use pivot tables is better.

If anyone cares, the following is the __tested__ macro....

Option Explicit

Sub doit()
Dim oldCalc, p0, src As Range, dst As Range
Dim n As Long, i As Long, j As Long

'***** modify these *****
Set src = Range("b1") 'cell with first postal code
Set dst = Range("e1") 'target cell for list, 2 columns

oldCalc = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'src = column of contiguous cells
Set src = Range(src, src.End(xlDown))
n = src.Count

'convert all to text for sort so that
'12345-1234 follows 12345, for example
'(Text To Columns, format as Text)
src.TextToColumns Destination:=dst, DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 2), TrailingMinusNumbers:=False

'sort in text order
dst.Resize(n).Sort Key1:=dst, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'create list of discrete postal codes
'count duplicates
p0 = dst.Resize(n)
dst.Resize(n, 2).Clear
ReDim p(1 To n, 1 To 2)
p(1, 1) = p0(1, 1): p(1, 2) = 1
j = 1
For i = 2 To n
If p0(i, 1) = p(j, 1) Then p(j, 2) = p(j, 2) + 1 _
Else j = j + 1: p(j, 1) = p0(i, 1): p(j, 2) = 1
Next
dst.Resize(j, 2) = p

Application.Calculation = oldCalc
Application.ScreenUpdating = True
End Sub
 
F

F

If anyone cares, the following is the __tested__ macro....

Option Explicit

Sub doit()
Dim oldCalc, p0, src As Range, dst As Range
Dim n As Long, i As Long, j As Long

'***** modify these *****
Set src = Range("b1") 'cell with first postal code
Set dst = Range("e1") 'target cell for list, 2 columns

oldCalc = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'src = column of contiguous cells
Set src = Range(src, src.End(xlDown))
n = src.Count

'convert all to text for sort so that
'12345-1234 follows 12345, for example
'(Text To Columns, format as Text)
src.TextToColumns Destination:=dst, DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 2), TrailingMinusNumbers:=False

'sort in text order
dst.Resize(n).Sort Key1:=dst, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'create list of discrete postal codes
'count duplicates
p0 = dst.Resize(n)
dst.Resize(n, 2).Clear
ReDim p(1 To n, 1 To 2)
p(1, 1) = p0(1, 1): p(1, 2) = 1
j = 1
For i = 2 To n
If p0(i, 1) = p(j, 1) Then p(j, 2) = p(j, 2) + 1 _
Else j = j + 1: p(j, 1) = p0(i, 1): p(j, 2) = 1
Next
dst.Resize(j, 2) = p

Application.Calculation = oldCalc
Application.ScreenUpdating = True
End Sub
Click to expand...

Thanks again: appreciated!

Unfortunately, I think I must be doing something wrong as I'm getting
just a count of how many entries there are in the column holding the
postcodes.

I have changed
Set src = Range("b1") 'cell with first postal code
Set dst = Range("e1") 'target cell for list, 2 columns

to
Set src = Worksheets("Aggregation").Range("dd9") 'cell with first postal
code
Set dst = Worksheets("Postcode analysis").Range("c2") 'target cell for
list, 2 columns

and am getting a 5 in D2 in the Postcode analysis sheet having entered 5
different postcodes:
AB17 2CD
AB17 3DE
AB17
AB17 4EF
AB17 5FG

Any pointers to where the error is would be welcome!
 
F

F

Probably the best idea. But it might be helpful to explain how. I
struggled with it myself, having never used pivot tables and finding
Walkenbach's "explanation" opaque as usual.

If the data are in B2:B65536 with a title in B1, click on Data> Pivot
Table.

1. In the first menu, be sure that "Where is the data?" is set to MS
Office Excel, and "What kind of report?" is set to Pivot Table.

2. In the next menu, select the range of the data, B:B or B1:B65536 in
my example. Note that B1 (the title) must be included in the range

3. In the next menu, select "Where do you want the report?".

4. Click Layout. Drag the button (with the title from B1) to the Data
area (becomes "count of title"). And drag the same button to the Row
area. Then click on OK.

5. Click Finish. Voila!

Detail.... In my case, the data is a mix of numerics (5-digit zip
codes) and text (5-hyphen-4-digit zip code). I don't like the way
that sorts. So I prefer to use Text To Column to convert everything
to text.

Apparently the Pivot Table does not track that change. So it must be
done __before__ creating the pivot table.
Click to expand...

Many thanks for the walk-through.

I have followed your instructions (I'm using the Office 2010 version of
Excel so have 'interpreted' them) and I'm seeing the pivot table results
which comprise a header - 'Row Labels' - with a drop-down menu and then
a listing of each of the postcodes found, with a footer - 'Grand Total'.

Each discrete postcode is present in the listing but there is no count
of the number of times any particular postcode was found. How can I add
this, please?

TIA.
 
J

joeu2004

I think I must be doing something wrong as I'm getting
just a count of how many entries there are in the column
holding the postcodes.
Click to expand...

No, my bad! I neglected to state the assumptions I made. Since your
postal codes include spaces, the Text To Column operation was not set
up properly.

Although I could fix that, it really is not necessary if your postal
codes are all text already.

The following modified macro seems to work.

-----

Option Explicit

Sub doit()
Dim oldCalc, p0, src As Range, dst As Range
Dim n As Long, i As Long, j As Long

'***** modify these Set statements *****
'cell with first postal code
Set src = Worksheets("sheet2").Range("b2")
'target cell for list, 2 columns
Set dst = Worksheets("sheet3").Range("e1")

oldCalc = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'src = column of contiguous cells
Set src = Range(src, src.End(xlDown))
n = src.Count
Set dst = dst.Resize(n)

'sort in text order if src is all text
dst = src.Value
dst.Sort Key1:=dst.Cells(1, 1), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'create list of discrete postal codes
'count duplicates
p0 = dst.Value
dst.Resize(n, 2).Clear
ReDim p(1 To n, 1 To 2)
p(1, 1) = p0(1, 1): p(1, 2) = 1
j = 1
For i = 2 To n
If p0(i, 1) = p(j, 1) Then p(j, 2) = p(j, 2) + 1 _
Else j = j + 1: p(j, 1) = p0(i, 1): p(j, 2) = 1
Next
dst.Resize(j, 2) = p

Application.Calculation = oldCalc
Application.ScreenUpdating = True
End Sub
 
F

F

No, my bad! I neglected to state the assumptions I made. Since your
postal codes include spaces, the Text To Column operation was not set
up properly.

Although I could fix that, it really is not necessary if your postal
codes are all text already.

The following modified macro seems to work.

-----

Option Explicit

Sub doit()
Dim oldCalc, p0, src As Range, dst As Range
Dim n As Long, i As Long, j As Long

'***** modify these Set statements *****
'cell with first postal code
Set src = Worksheets("sheet2").Range("b2")
'target cell for list, 2 columns
Set dst = Worksheets("sheet3").Range("e1")

oldCalc = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'src = column of contiguous cells
Set src = Range(src, src.End(xlDown))
n = src.Count
Set dst = dst.Resize(n)

'sort in text order if src is all text
dst = src.Value
dst.Sort Key1:=dst.Cells(1, 1), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'create list of discrete postal codes
'count duplicates
p0 = dst.Value
dst.Resize(n, 2).Clear
ReDim p(1 To n, 1 To 2)
p(1, 1) = p0(1, 1): p(1, 2) = 1
j = 1
For i = 2 To n
If p0(i, 1) = p(j, 1) Then p(j, 2) = p(j, 2) + 1 _
Else j = j + 1: p(j, 1) = p0(i, 1): p(j, 2) = 1
Next
dst.Resize(j, 2) = p

Application.Calculation = oldCalc
Application.ScreenUpdating = True
End Sub
Click to expand...

Thanks for the update: very much appreciated!
 
J

joeu2004

I'm using the Office 2010 version of Excel [....]
I'm seeing the pivot table results which comprise a
header - 'Row Labels' - with a drop-down menu and then
a listing of each of the postcodes found, with a footer
- 'Grand Total'. [....]
Each discrete postcode is present in the listing but
there is no count of the number of times any particular
postcode was found. How can I add this, please?
Click to expand...

Unfortunately, I have XL2003, not XL2010. So I might be of no use to
you, especially since I'm a novice with pivot table. Hopefully, Gord
or some other experienced pivot table user will chime in.

From your description, it almost sounds like you dragged the button to
the data area, but not __also__ to the row area. (Note that my Step 4
has __two__ instructions. Bad idea!)

However, when I make that mistake in XL2003, I do not see exactly what
you describe. So my supposition might be a misdirection.
 
F

F

Many thanks for the walk-through.

I have followed your instructions (I'm using the Office 2010 version of
Excel so have 'interpreted' them) and I'm seeing the pivot table results
which comprise a header - 'Row Labels' - with a drop-down menu and then
a listing of each of the postcodes found, with a footer - 'Grand Total'.

Each discrete postcode is present in the listing but there is no count
of the number of times any particular postcode was found. How can I add
this, please?
Click to expand...

Sorted!

I have found my error and the pivot table works well!
 
F

F

What was the error?

Forget to change Field Settings to "Count"?
Click to expand...

No, omitted to include the second column for the total!

Doh! I thought the pivot table would generate it for me.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Space at end of multi page list to accommodate new entries 1
Postcode group counting in query 2
Subject: transferring address postcodes to another table 2
transferring address postcodes to another table 2
Button on Form to find a file to use in query but not import- dbf,text,excel 1
Beginner - Excel - Auto look up values from worksheet 3 from User 0
Auto-lookup values between Sheet3 & UserForm & paste to Sheet1 2
HELP: Macro to conduct web query and return results 1

Top