Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Counting unique values between two columns

  1. #1
    Join Date
    10th February 2005
    Posts
    2

    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. #2
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,275

    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!
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    10th February 2005
    Posts
    2

    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. #4
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,275

    Re: Counting unique values between two columns

    Quote 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. #5
    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. #6
    Join Date
    28th February 2012
    Posts
    1

    Re: Counting unique values between two columns

    Quote Originally Posted by nub4057 View Post
    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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Topics: Counting Unique Values with Functions
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 9th, 2008, 18:17
  2. Replies: 2
    Last Post: January 17th, 2007, 02:03
  3. Counting unique values, 2 columns, alphanumeric
    By cdhowells in forum EXCEL HELP
    Replies: 5
    Last Post: January 16th, 2007, 23:16
  4. Using sumproduct for counting unique values
    By thoemmes in forum EXCEL HELP
    Replies: 2
    Last Post: June 28th, 2005, 23:30

Bookmarks

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