Top15 words on worksheet 1

[NFGnorbert]

Hi all,

Recently I found this post on MrExcel Using Thesaurus with VBA
Great tool, which I use to collect first associations with images. Since a synonym would also have done the job I decided to add up to 10 of them using the code mentioned above.
Currently I use it on worksheet 1 (only the first 11 colomns, since 10 synonyms are enough).

However, my worksheet 1 now has hundreds of lines. So, I would like the Top10 words of worksheet 1 to be reported on worksheet 3.

I fail to do this on my own... is there someone who can help me out?

Look forward to any help, Regards NFG.

 

[C Moore]

How are they ranked to be Top 10? Are you wanting the literal first 10 cells?

 

[NFGnorbert]

On worksheet 3 I would like to see the 10 words that occur most on worksheet 1 listed below each other like this:
Word 1 (most found): excitement
Word 2: pleasure
Etc.
Only the words would be sufficient, however if the number of times the word was found could be mentioned in the column next to it then it would be beyond expectation!

 

[C Moore]

Are you opposed to doing a COUNTIF on worksheet 1? Next to the word list add a countif column and then summarize off the numbers on worksheet 3.

 

[NFGnorbert]

Are you opposed to doing a COUNTIF on worksheet 1? Next to the word list add a countif column and then summarize off the numbers on worksheet 3.

The issue is that my datasheet with macro (worksheet 1) should stay untouched.
However, copying the worksheet 1 columns to worksheet 3 including extra columns should be no problem. Next to each word on worksheet 3 a COUNTIF could be added (with a search on worksheet 1). Afterwards its probably possible to make a pivot top 10 per column followed by a new pivot on the found top100? Or is their a more efficient solution?

 

[C Moore]

A pivot table removes the necessity of the helper column (many people aren't familiar with Pivot tables, and is a better solution than what I mentioned. Setup a dynamic named range for sheet 1 so that nothing actually 'touches' it. Word columns as row and as value can be sorted. Refresh as needed.

 

[NFGnorbert]

Thanks a lot for your help, problem solved.