Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Thread: Counting unique values between two columns

1. Counting unique values between two columns

Hello everyone, this formula problem has been twisting my brain for a while. I have two nonadjacent columns, one containing numbers, the other alphanumeric data. As an example:

B L <---column letters
==
1 a
1 a
1 a
1 b
1 c
3 b
4 b
5 a
5 a

Lets say the column with the numbers is column B, the letters are column L. There are 4 unique numbers in column B, and 4 unique letters in column L. However, I want to use column L as the criteria for counting the unique values in column B. By that I mean I want the count to say there are 6 unique numbers in col B: 1 has letters a, b and c; 3 has letter b; 4 has letter b; both 5's have letter a. The contents of the actual spreadsheet are more complex than single digits, its more like:

1945201537 LK 10/4/2004 10:05:16 AM-OK
1844871290 LK 10/4/2004 10:05:20 AM-OK
1645691251 LK 10/4/2004 10:05:25 AM-OK

A number and a timestamp with initials. Frequency by itself works fine on the first column, and I got this formula for counting the timestamp column:

=SUM(IF(FREQUENCY(IF(L2:L5<>"",MATCH(L2:L5,L2:L5,0)),ROW(INDIRECT("1:"&ROWS(L2:L5))))>0,1,0))

I've tried many variations of both to no avail. Anybody have a solution?

Excel Video Tutorials / Excel Dashboards Reports

2. Super M‌oderator
Join Date
4th July 2004
Location
Posts
2,371

Re: Counting unique values between two columns

First, enter a unique list of values from Column L into another column, let's say Column M, and enter the following formula in N1 and copy down:

=SUM(IF(FREQUENCY(IF(\$L\$1:\$L\$9=\$M1,\$B\$1:\$B\$9),\$B\$1:\$B\$9)>0,1,0))

...confirmed with CONTROL+SHIFT+ENTER.

Then sum these amounts to give you your unique count.

Note that you can use Advanced Filter to generate the unique list for Column M. Choose 'Copy to another location' and check 'Unique records only'.

Sample file attached...

Hope this helps!

Excel Video Tutorials / Excel Dashboards Reports

3. Re: Counting unique values between two columns

While that does work and I appreciate the suggestion, the thing is that the circumstances surrounding how I have to use it prevent me from using that method:-\ Since the column from the actual worksheet that has the alphanumeric data contains timestamps, there are MANY different values. Sometimes these sheets range from 200-900 rows or more. I need it in an equation because I need to place that equation in a separate workbook that contains reports on the counts I make. I realize I could just filter, copy the unique records to another sheet and hide the sheet, but it seems more efficient to have it in one equation. Actually, before I go off trying to find this proverbial holy grail, is this feasible in a single equation?

Excel Video Tutorials / Excel Dashboards Reports

4. Super M‌oderator
Join Date
4th July 2004
Location
Posts
2,371

Re: Counting unique values between two columns

Originally Posted by carg1
...is this feasible in a single equation?
I don't know if that's possible, but if it is it would probably be very inefficient. Maybe VBA would be the route to go. Hopefully someone with VBA experience will be able to help.

Cheers!

Excel Video Tutorials / Excel Dashboards Reports

5. I agreed to these rules
Join Date
19th July 2011
Posts
1

Re: Counting unique values between two columns

Fantastic. Just what I was looking for for my dataset. Thanks Domenic!!
Last edited by AAE; July 24th, 2011 at 01:38. Reason: delete quote

Excel Video Tutorials / Excel Dashboards Reports

6. I agreed to these rules
Join Date
28th February 2012
Posts
1

Re: Counting unique values between two columns

Originally Posted by nub4057
Fantastic. Just what I was looking for for my dataset. Thanks Domenic!!
+100000

That's what I was looking for 2 weeks... I just used the same formula without CTRL+SHIFT+ENTER and now I understand how important it is to put the Vlookup function in this equation thanks a lot...

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno