Hi,

I'm trying to count zip codes in a dynamic fashion. Is there a
function which will identify what zips are present assigning each 2
cells as follows:

A1 B1 C1 D1
88001 12
A2 B2 C2 D2
88002 48
A3 B3 C3 D3
88005 17

So basically figure out what is there and how many times it occures in
a column, I know I can set up a COUNTIF function, but I don't want to
have to do that each time I ad a zip and I only want the spreadsheet so
large... I'm sure I've missed someting obvious, but can't for the life
of me find it.

Thanks,

Byron

How about a Pivot Table?

HTH!


--
ExcelChampion
------------------------------------------------------------------------
ExcelChampion's Profile: http://www.officehelp.in/member.php?userid=5096
View this thread: http://www.officehelp.in/showthread.php?t=1262228

Posted from - http://www.officehelp.in

If all of the zips will be entered into column A, you could right click on
your sheet tab and paste this code into the code window to add the Countif
function as you go and remove the countif function as zips are deleted.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range

If Target.Column < 1 Or Target.Columns.Count 1 _
Then Exit Sub

Application.EnableEvents = False

For Each rngCell In Target.Cells
If Not IsEmpty(rngCell) Then
rngCell(1, 2).Formula = "=Countif(A:A," _
& rngCell.Address(False, False) & ")"
Else: rngCell(1, 2).ClearContents
End If
Next rngCell

Application.EnableEvents = True

End Sub

"Dos Equis" wrote:

Hi,

I'm trying to count zip codes in a dynamic fashion. Is there a
function which will identify what zips are present assigning each 2
cells as follows:

A1 B1 C1 D1
88001 12
A2 B2 C2 D2
88002 48
A3 B3 C3 D3
88005 17

So basically figure out what is there and how many times it occures in
a column, I know I can set up a COUNTIF function, but I don't want to
have to do that each time I ad a zip and I only want the spreadsheet so
large... I'm sure I've missed someting obvious, but can't for the life
of me find it.

Thanks,

Byron

JMB,

All zips go in column K so I think I should change the code to reflect:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range

If Target.Column < 11 Or Target.Columns.Count 11 _
Then Exit Sub

Application.EnableEvents = False

For Each rngCell In Target.Cells
If Not IsEmpty(rngCell) Then
rngCell(1, 2).Formula = "=Countif(A:A," _
& rngCell.Address(False, False) & ")"
Else: rngCell(1, 2).ClearContents
End If
Next rngCell

Application.EnableEvents = True

End Sub

I'm assuming thet (rngcell) is the range of cells I would want the zips
stored in such as W:W Would you please identify anything else I would
need to alter? Thank you,


Byron

JMB wrote:
If all of the zips will be entered into column A, you could right click on
your sheet tab and paste this code into the code window to add the Countif
function as you go and remove the countif function as zips are deleted.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range

If Target.Column < 1 Or Target.Columns.Count 1 _
Then Exit Sub

Application.EnableEvents = False

For Each rngCell In Target.Cells
If Not IsEmpty(rngCell) Then
rngCell(1, 2).Formula = "=Countif(A:A," _
& rngCell.Address(False, False) & ")"
Else: rngCell(1, 2).ClearContents
End If
Next rngCell

Application.EnableEvents = True

End Sub

"Dos Equis" wrote:

Hi,

I'm trying to count zip codes in a dynamic fashion. Is there a
function which will identify what zips are present assigning each 2
cells as follows:

A1 B1 C1 D1
88001 12
A2 B2 C2 D2
88002 48
A3 B3 C3 D3
88005 17

So basically figure out what is there and how many times it occures in
a column, I know I can set up a COUNTIF function, but I don't want to
have to do that each time I ad a zip and I only want the spreadsheet so
large... I'm sure I've missed someting obvious, but can't for the life
of me find it.

Thanks,

Byron

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range

If Target.Column < 11 Or Target.Columns.Count 1 _
Then Exit Sub

Application.EnableEvents = False

For Each rngCell In Target.Cells
If Not IsEmpty(rngCell) Then
rngCell(1, 13).Formula = "=Countif(K:K," _
& rngCell.Address(False, False) & ")"
Else: rngCell(1, 2).ClearContents
End If
Next rngCell

Application.EnableEvents = True

End Sub



rngCell is a placeholder for the For/Next loop.

For Each rngCell In Target.Cells

which is read as
"For Each *Item* In *Some Collection*

It loops through each cell in the Target Range (Target refers to whatever
cell was changed - which is why we test the column number to see if Target is
column K (or 11)). Whatever cell the loop is processing is referred to as
rngCell (a name that I made up) in the code inside the loop.



"Dos Equis" wrote:

JMB,

All zips go in column K so I think I should change the code to reflect:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range

If Target.Column < 11 Or Target.Columns.Count 11 _
Then Exit Sub

Application.EnableEvents = False

For Each rngCell In Target.Cells
If Not IsEmpty(rngCell) Then
rngCell(1, 2).Formula = "=Countif(A:A," _
& rngCell.Address(False, False) & ")"
Else: rngCell(1, 2).ClearContents
End If
Next rngCell

Application.EnableEvents = True

End Sub

I'm assuming thet (rngcell) is the range of cells I would want the zips
stored in such as W:W Would you please identify anything else I would
need to alter? Thank you,


Byron

JMB wrote:
If all of the zips will be entered into column A, you could right click on
your sheet tab and paste this code into the code window to add the Countif
function as you go and remove the countif function as zips are deleted.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range

If Target.Column < 1 Or Target.Columns.Count 1 _
Then Exit Sub

Application.EnableEvents = False

For Each rngCell In Target.Cells
If Not IsEmpty(rngCell) Then
rngCell(1, 2).Formula = "=Countif(A:A," _
& rngCell.Address(False, False) & ")"
Else: rngCell(1, 2).ClearContents
End If
Next rngCell

Application.EnableEvents = True

End Sub

"Dos Equis" wrote:

Hi,

I'm trying to count zip codes in a dynamic fashion. Is there a
function which will identify what zips are present assigning each 2
cells as follows:

A1 B1 C1 D1
88001 12
A2 B2 C2 D2
88002 48
A3 B3 C3 D3
88005 17

So basically figure out what is there and how many times it occures in
a column, I know I can set up a COUNTIF function, but I don't want to
have to do that each time I ad a zip and I only want the spreadsheet so
large... I'm sure I've missed someting obvious, but can't for the life
of me find it.

Thanks,

Byron