Solved

# Count Unique Values

Posted on 2011-05-09
224 Views
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
0
Question by:matt_m

LVL 41

Expert Comment

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

http:/Q_26934042.html

Dave
0

LVL 41

Expert Comment

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
0

LVL 41

Accepted Solution

Here's that example in the attached:

Dave
UniqueFromMultipleColumns-r1.xls
0

LVL 41

Expert Comment

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
0

Author Closing Comment

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

## Featured Post

Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.