[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 274

# 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
0
matt_m
• 4
1 Solution

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

http:/Q_26934042.html

Dave
0

Commented:
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

Commented:
Here's that example in the attached:

Dave
UniqueFromMultipleColumns-r1.xls
0

Commented:
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 Commented:
Thanks Dave work s perfectly! I wish I asked this question yesterday; it owuld have save me a lot of time!
0

## Featured Post

• 4
Tackle projects and never again get stuck behind a technical roadblock.