Count amount of diff values in column

[gamuzadt]

Hi all,

I need help with counting unique values in a column, taking into consideration info from another column.

Input example:

Column A _________ Column B
Ford _____________ John
Mustang __________ Mike
Honda ____________ Ralph
GM _______________ Ralph
Ford ______________ Matt
Mustang __________ Ralph
Ford ______________ Carl
Mustang ___________ Robert
Ford _______________ Ralph
Honda ______________ Ralph
Honda ______________ Ralph
GM _________________ Carl


As seen in the output below, I need a table with each brand in column A, and how many DIFFERENT people matches the data in column B


Output example:

Column A ____________ Column B
Ford ________________ 4
Mustang _____________ 3
Honda _______________ 1
GM __________________ 2

As shown in this example, all the "Honda"s in column A are assigned to only 1 person (Ralph) so column B must say "1"

Id appreciate any help you can provide me with this.

(Sorry if the explanation is not clear enough, english is not my native language)

Regards.

[Palmetto]

Have you considered using a Pivot Table?

[DonkeyOte]

You can use either a FREQUENCY array or a SUMPRODUCT along the lines of the below

Please Login or Register  to view this content.

where A1 holds Ford etc...
(you may need to alter delimiters from , to ; pending your locale)

re: Pivot Table - won't give a unique count per se.

[gamuzadt]

Tried too, but im kind of new to pivot tables, where should I drag the fields to?

Thanks for the quick response.

[MickG]

Hi, Try this, Result start Column "D".

Please Login or Register  to view this content.

Regards Mick

PS:-To Run Code.
Right Click Sheet Tab , Select "View Code" , (VB Window appears.)
On VB Window ToolBar, Click "Insert", "Module", (New Window appears.)
Paste Code In New Window..
Close VB Window.
In WorkSheet Click "Alt+F8" Macro Dialog Box Appears.
Select Code From List, Click "Run", On R/H/S of Dialog Box
Code should now be Executed.

[gamuzadt]

I think DonkeyOte's way will work for now, thanks everyone for the fast response!

[gamuzadt]

I think DonkeyOte's way will work for now, thanks everyone for the fast response!

I have a problem, this method works but is also counting blank spaces. Is there a way to prevent this from happening?

Regards,

[DonkeyOte]

Can you post an example illustrating the fact ?

I presume you mean you have criteria values listed in A without any associated value in B ? If so:

Please Login or Register  to view this content.

If you mean that you get a result even if criteria is blank then add a pre-emptive IF to the function as a whole, eg:

Please Login or Register  to view this content.

[gamuzadt]

You presumed correctly, first method worked.
DonkeyOte, thanks a lot!