I have a worksheet with some 30,000 rows of data. This worksheet consists of
columns with the information: FirstName, LastName, City, State, Country,
etc...

-I would like to find the top 50 to 60 cities with the most number of users.
-Can I also somehow include different spellings for the cities such as "St
Louis", "St. Louis", "Saint Louis", and misspells "Saint Luis?"
-Can I group this information in a chart/table/ or graph consisting of City,
State, Number of Users, and percentage of the whole?

Help would be greatly appreciated

Would a pivot table work?

On Oct 9, 1:43 pm, J Pol <J wrote:
I have a worksheet with some 30,000 rows of data. This worksheet consists of
columns with the information: FirstName, LastName, City, State, Country,
etc...

-I would like to find the top 50 to 60 cities with the most number of users.
-Can I also somehow include different spellings for the cities such as "St
Louis", "St. Louis", "Saint Louis", and misspells "Saint Luis?"
-Can I group this information in a chart/table/ or graph consisting of City,
State, Number of Users, and percentage of the whole?

Help would be greatly appreciated

Yes, thank you. But now how do I consolidate two rows that are the same, but
may be spelled different or wrong. For example it is counting Mobile, Alabama
and Mobiel, Alabama as two seperate things, but it is apparent that it is
misspelled. Even more discrepancy happens for cities such as St. Louis, Saint
Louis, St Louis.

"dan dungan" wrote:

Would a pivot table work?

On Oct 9, 1:43 pm, J Pol <J wrote:
I have a worksheet with some 30,000 rows of data. This worksheet consists of
columns with the information: FirstName, LastName, City, State, Country,
etc...

-I would like to find the top 50 to 60 cities with the most number of users.
-Can I also somehow include different spellings for the cities such as "St
Louis", "St. Louis", "Saint Louis", and misspells "Saint Luis?"
-Can I group this information in a chart/table/ or graph consisting of City,
State, Number of Users, and percentage of the whole?

Help would be greatly appreciated

Hi J Pol,

I use the pivot table to identify the misspelled words.

Then, I use find, replace to correct.

If it is active data, then I look for ways to control the data entry.

How is this data getting into your spreadsheet?

Dan


On Oct 10, 7:41 am, J Pol wrote:
Yes, thank you. But now how do I consolidate two rows that are the same, but
may be spelled different or wrong. For example it is counting Mobile, Alabama
and Mobiel, Alabama as two seperate things, but it is apparent that it is
misspelled. Even more discrepancy happens for cities such as St. Louis, Saint
Louis, St Louis.