+ Reply to Thread
Results 1 to 12 of 12

Trying to count unique values for one column when another column is equal to a cell

  1. #1
    Registered User
    Join Date
    01-17-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    6

    Trying to count unique values for one column when another column is equal to a cell

    Hello! A quick run-down...I have claim numbers in column A, procedure codes in column B and units in column C. I've pulled out the unique procedure codes and put them in column E and now need to know the count of unique claim numbers in column A where column B equals the (variable) procedure codes in G. I have to be missing something, b/c I can't imagine this is as difficult as I'm making it.


    untitled.JPG

    Any help is GREATLY appreciated!

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Trying to count unique values for one column when another column is equal to a cell

    Try this in cell F2:

    =SUM(($B$2:$B$6=E2)*(IFERROR(MATCH($A$2:$A$6,INDEX(IF($B$1:$B$6=E2,$A$1:$A$6),0),0),0)=ROW($A$2:$A$6)))

    Confirmed with Ctrl-Shift-Enter, not just Enter.

    So, pretty difficult

  3. #3
    Registered User
    Join Date
    01-17-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Trying to count unique values for one column when another column is equal to a cell

    YES!!! That is perfect! Thank you SO MUCH! I've been driving myself batty thinking I was missing something simple!

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trying to count unique values for one column when another column is equal to a cell

    Here's another one...

    Array entered** in F2 and copied down:

    =SUM(IF(FREQUENCY(IF(B$2:B$6=E2,MATCH(A$2:A$6,A$2:A$6,0)),ROW(A$2:A$6)-ROW(A$2)+1),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    01-17-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Trying to count unique values for one column when another column is equal to a cell

    Thanks, Tony! That one worked as well!

    Do either of you know which of these two formulas processes fastest? I'm working with a little over 37K lines total. Trying to scale down as much as possible before using the array, but sheesh! It's still taking forever to run.

    Thank you again for the help!

  6. #6
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Trying to count unique values for one column when another column is equal to a cell

    In which case I'd be tempted to use a couple of helper columns, rather than complicated array formula.

    Can you post a sample workbook? It's a bit of a pain having to retype your data.

  7. #7
    Registered User
    Join Date
    01-17-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Trying to count unique values for one column when another column is equal to a cell

    Sorry about that. I've attached a sample, Andrew. I'm a newbie here, so please let me know if I didn't do it correctly.

    Thanks again for your help!
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Trying to count unique values for one column when another column is equal to a cell

    OK, right back at you ... I've added a hidden column (D) which is, effectively, counting unique values.

    This makes all of the formula much simpler (and, therefore, faster). You'll have to try it against your full data set to see how it does.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-17-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Trying to count unique values for one column when another column is equal to a cell

    Aha! Brilliant! And yes, this speeds the process along significantly. You're amazing! Have a wonderful weekend!

  10. #10
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: Trying to count unique values for one column when another column is equal to a cell

    @andrew-r, for some reason, you are getting 2 for 58552, whereas i am getting 1 for the same. when i manually check the data, 1 seems to be the right answer. also, because of concatenation, if you were to have blanks in column A, then the results would be incorrect.

    here is another approach...
    Attached Files Attached Files
    Last edited by icestationzbra; 01-18-2013 at 02:52 PM.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  11. #11
    Registered User
    Join Date
    01-17-2013
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Trying to count unique values for one column when another column is equal to a cell

    Andrew - Rats! I spoke too soon. In spotchecks, it isn't completely holding up. Just needs tweaking, thought, I think, and definitely a better solution than an array, given the size of the data.

    icestationzbra - Thanks! I'll have a look!

    I really appreciate everyone's help! Wish this forum had been around when I FIRST started using Excel.

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trying to count unique values for one column when another column is equal to a cell

    Quote Originally Posted by michbek View Post
    Thanks, Tony! That one worked as well!

    Do either of you know which of these two formulas processes fastest? I'm working with a little over 37K lines total. Trying to scale down as much as possible before using the array, but sheesh! It's still taking forever to run.

    Thank you again for the help!
    I would have to test the formulas in the actual file.

    If you're interested there is calculation timer code here.

    In general, doing calculations on unique entries in large datasets is calculation intensive.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1