Count duplicates and put their number in next column

[wali]

Hello,

I have a very large wordlist in excel which looks like:

Column.A
love
love
life
store
who
zebra
love
store

Now I would like to have the number of frequency of each word in Column B. For example the word "love" is three times there and the word "store" two times. I need it to look like:

Column.A------------Column.B
love-------------------3
love-------------------3
life---------------------1
store------------------2
who-------------------1
zebra-----------------1
love-------------------3
store------------------2

Thank you for ur help in advance.

[zbor]

Try in B2: =COUNTIF(A:A, A2) and pull down

[ConneXionLost]

Hi,

Try this:

Please Login or Register  to view this content.

Cheers,

[wali]

It works!! Thank you both

[wali]

I have a problem with the formula. Its too slow. I have about 0.5 Million entries and it is lasting and lasting and lasting!! I am not sure if it will finish in hours/days.
Is there a faster way to do the job?

[zbor]

This is fastest formula...

Maybe you could create unque list of entries so it wont show you

Please Login or Register  to view this content.

but

Please Login or Register  to view this content.

Reucing number of formulas

[DonkeyOte]

Use a Pivot Table - set the word as both Row Field and Data Field (will default to Count) - you will need column header value in A1.

You can then use GETPIVOTDATA to retrieve the value in Column B.

Alternatively sort your Data A-Z and use more efficient formulae
(ie for first instance of given word use binary search to find last instance and compare row #'s - thereafter use prior result)

[DonkeyOte]

Figured I'd add a basic attachment to illustrate both.

Sorting data is often the key to really efficient calculations when working with mass data.

[wali]

Yes, it helped alot after I sorted the list. Thank you very much for taking the time for my problem