subtotal or count

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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
 
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.
 
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
Data>Filter>Autofilter.
Use the dropdown on City>Contains 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.
 
Back
Top