Grouping/Counting/Concentrations

[Guest]

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

 

[dan dungan]

Would a pivot table work?

 

[Guest]

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]

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