Thread: Display Number of Occurances from Unique List Against Bulk List

Hi All

I have a couple of problems that are beyond my excel knowledge; I would much appreciate it if any could help me on this?

I have one bulk list and one unique list that I have removed the duplicates out of. Now what I would like to do I compare my unique list to the bulk list and in another column display how many instances of each word in the unique column are in the bulk list.

For instance next to each word in the unique list I would like to it display a number, so a 3 next to my unique list would mean that particular word was in the bulk list three times?

Can anyone figure out a method to achieve this?

Thanks again Marx

Take alook at the example attached.
Let me know if this helps.
modytrane

Wow thats great, seems to be doing the job, one thing I cant figure out is how to expand the furthest left complete list box. When I edit the unique list formula it puts a blue box around your list, how do I expand this list as mine is a few thousand long ?

Thanks Marx

Originally Posted by Ron Coderre

With
A2:A1000 containing individual words (duplicates and blanks allowed)
and
B2:B30 containing a list of unique words

This formula returns the count of the B2 words that are in A2:A1000

C2: =COUNTIF($A$2:$A$1000,B2)

Copy C2 down as far as you need.

Does that help?

You legend, that has done it

Ron's solution is simple enough, as long as it helps you.

My example creates unique list automatically. So if you add few items to your master list, all unique items will be added and occurances calculated for you.

You can define your master list range in D5 and D6. Also, change the input range for "rngUserInput". Follow instructions on the attached sheet.

Good Luck,

modytrane