Counting (unknown at present) discrete values
 

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

--
F

Counting (unknown at present) discrete values
 

Pivot Table would be useful.


Gord Dibben MS Excel MVP

On Fri, 06 May 2011 21:28:50 +0100, F <news@nowhere wrote:

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

Counting (unknown at present) discrete values
 

On May 6, 1:28*pm, F <news@nowhere wrote:
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?

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

Counting (unknown at present) discrete values
 

On May 6, 4:49*pm, Gord Dibben wrote:
Pivot Table would be useful.

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.

Counting (unknown at present) discrete values
 

joeu2004

When struggling to create your next PT take a gander at Debra's PT site for
really great assistance.

http://www.contextures.on.ca/tiptech.html#P


Gord

On Fri, 6 May 2011 19:49:50 -0700 (PDT), joeu2004 wrote:

On May 6, 4:49*pm, Gord Dibben wrote:
Pivot Table would be useful.

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.

Counting (unknown at present) discrete values
 

On 07/05/2011 02:14 joeu2004 wrote:

On May 6, 1:28 pm, F<news@nowhere wrote:
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?

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

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...

--
F

Counting (unknown at present) discrete values
 

src.TextToColumns Destination:=trg

trg ? dst, i believe.

Counting (unknown at present) discrete values
 

On May 7, 4:50*am, darkblue wrote:
src.TextToColumns Destination:=trg

trg ? dst, i believe.

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

Counting (unknown at present) discrete values
 

On 07/05/2011 15:40 joeu2004 wrote:

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

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

Counting (unknown at present) discrete values
 

On 07/05/2011 03:49 joeu2004 wrote:

On May 6, 4:49 pm, Gord wrote:
Pivot Table would be useful.

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.

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.

--
F

Counting (unknown at present) discrete values
 

On May 7, 11:49*am, F <news@nowhere wrote:
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.

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

Counting (unknown at present) discrete values
 

On 07/05/2011 21:14 joeu2004 wrote:

On May 7, 11:49 am, F<news@nowhere wrote:
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.

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

Thanks for the update: very much appreciated!

--
F

Counting (unknown at present) discrete values
 

On May 7, 12:25*pm, F <news@nowhere wrote:
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?

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.

Counting (unknown at present) discrete values
 

On 07/05/2011 20:25 F wrote:

On 07/05/2011 03:49 joeu2004 wrote:

On May 6, 4:49 pm, Gord wrote:
Pivot Table would be useful.

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.

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?

Sorted!

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

--
F

Counting (unknown at present) discrete values
 

What was the error?

Forget to change Field Settings to "Count"?


Gord

On Sat, 07 May 2011 22:56:48 +0100, F <news@nowhere wrote:

Sorted!

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

--
F

Counting (unknown at present) discrete values
 

On 07/05/2011 23:41 Gord Dibben wrote:

What was the error?

Forget to change Field Settings to "Count"?

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

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

--
F