How to report number of UNIQUE cells on new sheet?

[JGCA]

On worksheet1 I have the following

joe | apple
joe | pear
joe | lettuce
joe | orange
tom| apple
tom| lettuce
abe| apple
abe| orange
abe| lemon


On worksheet2 I would like

apple | 3
pear | 1
lettuce | 2
orange | 2
lemon | 1


Anyone know how I could accomplish this?

Thanks

[jmicdk]

SUMPRODUCT() will work for this

[jindon]

Please Login or Register  to view this content.

[JGCA]

SUMPRODUCT() will work for this

How exactly? for my real project, there are many more categories than apple, pear, lettuce, orange, lemon - there are 100's.

[JGCA]

Please Login or Register  to view this content.

When I try to use this code I get:

"Compile error: sub or function not defined" and UBaound is highlighted.

Thanks

[TonyS]

Hi,
Another solution.

Please Login or Register  to view this content.

Tony

[JGCA]

Awesome, thanks!

[JGCA]

Hi,
Another solution.

Please Login or Register  to view this content.

Tony

(my column that I want to unique from is actually column F)... then I get subscript out of range.

[TonyS]

Hi,
No problem.

Please Login or Register  to view this content.

Tony

[JGCA]

Thanks Tony! Very helpful

[jindon]

When I try to use this code I get:

"Compile error: sub or function not defined" and UBaound is highlighted.

Thanks

typo
shoudl be UBound w/o "a"

Use my code when you have many data to work with, like over 5000 ro so.
You will notice the difference of the speed...