Link to home
Start Free TrialLog in
Avatar of matt_m
matt_m

asked on

Count Unique Values

Hi All,

But of a problem I've encountered here, I have two columns of data that I am preparing a summary report from.  

- In one of the columns I have a 5 or so different string values (from a defined group of 5 string values) which do repeat (eg A, A, C, A, C, D, E)

- the other column has values from an undefined group and can have repeating values (12,12,5,100,5, 12, 15,)

What I need to know is for each of the defined string values in the first Column how many unique values there are in the other column.  For example using the simplified example above "A" would have 2 unique values 12 and 100.  Alos a few things I should mention:

- I can't combine the two columns and analyse that
- I can't use a macro but I can use a VBA function if formulas can't solve this (VBA functions is the approach I have been trying).

Thanks for looking at my question and if you neeed anymore info let me know.

Cheers
Matt
Avatar of dlmille
dlmille
Flag of United States of America image

Here's a good discussion on a formula for counting unique values:

http:/Q_26934042.html

Dave
I'm going to give you a couple examples:

Here's one using FREQUENCY:

=SUM(IF(FREQUENCY(IF($A$2:$A$1000=$A2,$B$2:$B$1000),$B$2:$B$1000)>0,1,0)) - put this in column C (assuming 1000 rows, with header in row 1) and hit CTRL-SHIFT-ENTER to confirm array formula.  Then copy down.

It will provide the # of unique "A" or "C" or whatever combinations with the values you have...

Dave
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
And here's a more efficient formula (its built using SUMPRODUCT - already an array formula) - paste this in C2 and copy down for the same results:

=SUMPRODUCT(($A$2:$A$1000=$A2)*(MATCH($A$2:$A$1000&$B$2:$B$1000,$A$2:$A$1000&$B$2:$B$1000,0)=ROW($A$2:$A$1000)-1))

See attached for both examples.

Enjoy!

Dave
UniqueFromMultipleColumns-r1.xls
Avatar of matt_m
matt_m

ASKER

Thanks Dave work s perfectly! I wish I asked this question yesterday; it owuld have save me a lot of time!