Count the number of times each word in a column appears

Craig
Craig used Ask the Experts™
on
Hi all,
Hoping someone can guide me with what I thought would be a straightforward Excel calculation:
Say I have a column containing 10,000 first names.  I want to have Excel count how many times each name appears and have that count appear in another column.  
Can someone point me to the simplest solution?
Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Assuming First Names are in column A starting from Row#2, then try this...

In B2 (or in some other cell in row2)
=COUNTIF(A:A,A2)

Open in new window

and then copy it down.

Otherwise, you can simply insert a Pivot Table and drag the First Name field in the Row Area and drag the First Name field again in the Values Area.

Author

Commented:
Thanks Subodh.  Do I have to identify each criterion (that is, go through the entire column to identify each name that appears)?
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You're welcome Craig!

Didn't quite get your question, but as per the formula if you place the formula in column B assuming First Names are in column A, each cell in column B will display the count of occurrences of corresponding first name in column A.

Please refer to the attached with three sheets.
The first sheet in the attached has the proposed formula in column B.
The second sheet contains a Pivot Table which shows all the unique first names and their respective count.
In third sheet, data is converted into an Excel Table and named as "Data" and then the formula is placed in column B and a Pivot Table is inserted based on the data table. The advantage of using an Excel Table here is, if you add/delete data, when you refresh the Pivot Table, it will show you the updated data automatically.
Count-First-Names.xlsx

Author

Commented:
Subdoh,
I got it using the Pivot Table option!
Thanks much!
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome again Craig!
Great! Pivot Table was most suitable for this scenario.
Thanks for the testimonial and feedback Craig!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial