subtotal or count
 

Zip City
55901 Mytown
55901 Mytown,
55906 Yourtown
55906 Yourtown,
55906 Yourtown,
I subtotal on Zip code and use Count function
This gives a new column with subtotal in this format:
55901 Count 2
55906 Count 3
My objective is to subtotal on zip code but wish to see the label be the
'City'
i.e
Mytown Count 2,
Yourtown, Count 3
Any suggestions would be much appreciated.
I am using MS Excel 2000.
Tosh.

subtotal or count
 

Hi

Use the first dropdown in the Subtotal dialogue to select City instead of
ZIp.

--
Regards
Roger Govier


"Tosh" wrote in message
...
Zip City
55901 Mytown
55901 Mytown,
55906 Yourtown
55906 Yourtown,
55906 Yourtown,
I subtotal on Zip code and use Count function
This gives a new column with subtotal in this format:
55901 Count 2
55906 Count 3
My objective is to subtotal on zip code but wish to see the label be the
'City'
i.e
Mytown Count 2,
Yourtown, Count 3
Any suggestions would be much appreciated.
I am using MS Excel 2000.
Tosh.

subtotal or count
 

Hi Roger, thanks for replying to my post.
When I select City instead of Zip then I get:
Zip City
55901 Mytown
Mytown Count 1
55901 Mytown,
Mytown, Count 1
55906 Yourtown
Yourtown Count 1
55906 Yourtown,

"Mytown" and "Mytown," is counted as different cities.
My intent is to list 2 cities for zip code 55901 - even though the spelling
of city
might be incorrect. i.e "Mytown" or "Mytown," - notice the comma.

"Roger Govier" wrote:

Hi

Use the first dropdown in the Subtotal dialogue to select City instead of
ZIp.

--
Regards
Roger Govier


"Tosh" wrote in message
...
Zip City
55901 Mytown
55901 Mytown,
55906 Yourtown
55906 Yourtown,
55906 Yourtown,
I subtotal on Zip code and use Count function
This gives a new column with subtotal in this format:
55901 Count 2
55906 Count 3
My objective is to subtotal on zip code but wish to see the label be the
'City'
i.e
Mytown Count 2,
Yourtown, Count 3
Any suggestions would be much appreciated.
I am using MS Excel 2000.
Tosh.

subtotal or count
 

Hi
Excel will treat those as 2 separate occurrences of the city name, hence
that is the result you will get.
You could use Control +H to bring up Find and Replace.
Replace ,
With leave blank
Replace All.
Then carry out your Subtotal

--
Regards
Roger Govier


"Tosh" wrote in message
...
Hi Roger, thanks for replying to my post.
When I select City instead of Zip then I get:
Zip City
55901 Mytown
Mytown Count 1
55901 Mytown,
Mytown, Count 1
55906 Yourtown
Yourtown Count 1
55906 Yourtown,

"Mytown" and "Mytown," is counted as different cities.
My intent is to list 2 cities for zip code 55901 - even though the
spelling
of city
might be incorrect. i.e "Mytown" or "Mytown," - notice the comma.

"Roger Govier" wrote:

Hi

Use the first dropdown in the Subtotal dialogue to select City instead of
ZIp.

--
Regards
Roger Govier


"Tosh" wrote in message
...
Zip City
55901 Mytown
55901 Mytown,
55906 Yourtown
55906 Yourtown,
55906 Yourtown,
I subtotal on Zip code and use Count function
This gives a new column with subtotal in this format:
55901 Count 2
55906 Count 3
My objective is to subtotal on zip code but wish to see the label be
the
'City'
i.e
Mytown Count 2,
Yourtown, Count 3
Any suggestions would be much appreciated.
I am using MS Excel 2000.
Tosh.

subtotal or count
 

Hi,
For the senario I posted, your suggestion will be suffice.
In reality the data set will be much larger, in the thousands.
The value for city is entered in a data entry form and there no easy way to
validate the spelling. There is a good chance user input will have
mispellings for city names thus using Find/Replace will not work since it
isn't known which character to find and replace.
Meanwhile, I have been playing with pivot tables but again I keep coming up
to same result that I described in my original question.
Might have to start reading on how to write a script or VB program - I
hestitate as I have no previous experience of writing scripts or programs for
Excel.
Regards.

"Roger Govier" wrote:

Hi
Excel will treat those as 2 separate occurrences of the city name, hence
that is the result you will get.
You could use Control +H to bring up Find and Replace.
Replace ,
With leave blank
Replace All.
Then carry out your Subtotal

--
Regards
Roger Govier


"Tosh" wrote in message
...
Hi Roger, thanks for replying to my post.
When I select City instead of Zip then I get:
Zip City
55901 Mytown
Mytown Count 1
55901 Mytown,
Mytown, Count 1
55906 Yourtown
Yourtown Count 1
55906 Yourtown,

"Mytown" and "Mytown," is counted as different cities.
My intent is to list 2 cities for zip code 55901 - even though the
spelling
of city
might be incorrect. i.e "Mytown" or "Mytown," - notice the comma.

"Roger Govier" wrote:

Hi

Use the first dropdown in the Subtotal dialogue to select City instead of
ZIp.

--
Regards
Roger Govier


"Tosh" wrote in message
...
Zip City
55901 Mytown
55901 Mytown,
55906 Yourtown
55906 Yourtown,
55906 Yourtown,
I subtotal on Zip code and use Count function
This gives a new column with subtotal in this format:
55901 Count 2
55906 Count 3
My objective is to subtotal on zip code but wish to see the label be
the
'City'
i.e
Mytown Count 2,
Yourtown, Count 3
Any suggestions would be much appreciated.
I am using MS Excel 2000.
Tosh.

subtotal or count
 

Hi

Unless you can get some consistency with your data, you are going to
struggle, whatever method you try to use.
Try using Data Validation on the inputs to get consistency.
Take a look at Debra Dalgleish's site
http://www.contextures.com/xlDataVal01.html

One way to clean existing data, would be to have a header row, then use
DataFilterAutofilter.
Use the dropdown on CityContains Mytown
Overtype the first entry with the correct value you wish to see for that
town, then use the fill handle at the bottom right of the first visible cell
to drag that amended value down through other visible cells.
Repeat the procedure for your other values.

--
Regards
Roger Govier


"Tosh" wrote in message
...
Hi,
For the senario I posted, your suggestion will be suffice.
In reality the data set will be much larger, in the thousands.
The value for city is entered in a data entry form and there no easy way
to
validate the spelling. There is a good chance user input will have
mispellings for city names thus using Find/Replace will not work since it
isn't known which character to find and replace.
Meanwhile, I have been playing with pivot tables but again I keep coming
up
to same result that I described in my original question.
Might have to start reading on how to write a script or VB program - I
hestitate as I have no previous experience of writing scripts or programs
for
Excel.
Regards.

"Roger Govier" wrote:

Hi
Excel will treat those as 2 separate occurrences of the city name, hence
that is the result you will get.
You could use Control +H to bring up Find and Replace.
Replace ,
With leave blank
Replace All.
Then carry out your Subtotal

--
Regards
Roger Govier


"Tosh" wrote in message
...
Hi Roger, thanks for replying to my post.
When I select City instead of Zip then I get:
Zip City
55901 Mytown
Mytown Count 1
55901 Mytown,
Mytown, Count 1
55906 Yourtown
Yourtown Count 1
55906 Yourtown,

"Mytown" and "Mytown," is counted as different cities.
My intent is to list 2 cities for zip code 55901 - even though the
spelling
of city
might be incorrect. i.e "Mytown" or "Mytown," - notice the comma.

"Roger Govier" wrote:

Hi

Use the first dropdown in the Subtotal dialogue to select City instead
of
ZIp.

--
Regards
Roger Govier


"Tosh" wrote in message
...
Zip City
55901 Mytown
55901 Mytown,
55906 Yourtown
55906 Yourtown,
55906 Yourtown,
I subtotal on Zip code and use Count function
This gives a new column with subtotal in this format:
55901 Count 2
55906 Count 3
My objective is to subtotal on zip code but wish to see the label be
the
'City'
i.e
Mytown Count 2,
Yourtown, Count 3
Any suggestions would be much appreciated.
I am using MS Excel 2000.
Tosh.