count duplicates in Column E in all worksheets and write their number in Column H

[wali]

Hi,
i have a very large wordlist which is devided into many worksheets. These words are in Column E. For example:

Column E:

apple
tomato
sweets
apple
apple
tomato
apple

Now i need that the duplicates in column E in all worksheets are counted and their number is written in Column H. The list should then look like after running the macro:

Column E.........Column H

apple............... 4
tomato............. 2
sweets............. 1
apple............... 4
apple............... 4
tomato............. 2
apple............... 4

Thank you very much for each help in advance.

[berlan]

With formula you could try,

H2: =COUNTIF(E:E,E2)

and copy down

With vba, try something like,

Please Login or Register  to view this content.

[wali]

dear berlan, thank you very much for your suggestion. I need to count duplicates in all worksheets. how do i do that?

[nilem]

maybe so

Please Login or Register  to view this content.

[berlan]

To do the very same on col. E on all sheets, maybe:

Please Login or Register  to view this content.

[wali]

Sorry i was not clear enough. With all sheets i meant that the word is counted in all worksheets. It means that i need total number of "apple" all sheets in column H.

for example:

apple is 20 times in worksheet1, 30 times in worksheet2 and 2 times in worksheet3, then i need in each corresponding H cell the number 52.

[wali]

Thank you both
the code of nilem worked for me. You both are great. Thanks