+ Reply to Thread
Results 1 to 21 of 21

unique count help

  1. #1
    Registered User
    Join Date
    01-18-2020
    Location
    toronto
    MS-Off Ver
    2016
    Posts
    7

    Exclamation unique count help

    hi there:
    I need help with these for my work. I have attached the file.
    So for values based on 'column A' and 'column B', i need unique count in 'column C'.
    I couldn’t figure out a way for this. I can easily do it in pivot. But i need a formula for it.
    So lets say for John he has 3 cause and 2 unique cause. I want 2 for John in 'column C' but only in one row for John. Not for all John's row.
    In the attached file I have given an example, how I really want it!

    I also have posted a possible solution. But that solution will post the value in every row instead of only the 1st row for 'column A' value

    I really appreciate anyone helping me with this. Thanks in Advance.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    965

    Re: unique count help

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    638

    Re: unique count help

    A
    B
    C
    D
    E
    1
    Claimant cause Unique List unique cause
    2
    tom Unknown don
    1
    3
    tom Minor John
    2
    4
    tom High/CAT Impairment mike
    2
    5
    John Unknown tom
    3
    6
    John Major (hospitalization)
    7
    John Unknown
    8
    don Minor
    9
    don Minor
    10
    don Minor
    11
    mike Unknown
    12
    mike Minor



    D2=IFERROR(INDEX(A$2:A$100,MATCH(0,INDEX(COUNTIF(A$2:A$100,"<"&A$2:A$100)-SUMPRODUCT(COUNTIF($D$1:D1,A$2:A$100)),),0)),"")

    copy down


    E2=IF($D2<>"",SUM(IF(FREQUENCY(IF($A$2:$A$100=$D2,IF($B$2:$B$100<>"",MATCH($B$2:$B$12,$B$2:$B$100,0))),ROW($B$2:$B$100)-ROW($B$2)+1),1)),"")

    Control+shift+enter

    copy down

  4. #4
    Registered User
    Join Date
    01-18-2020
    Location
    toronto
    MS-Off Ver
    2016
    Posts
    7

    Re: unique count help

    BMV
    Thank you so much. but i am getting formula error. is there something wrong it there?
    carcalla
    Thats really cool what you have done. But i dont want unique list. I still want all rows.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    965

    Re: unique count help

    Sorry, it's array formula
    Ctrl+Shift+Enter in stand of Enter, and ";" could be replaced to ","
    Attached Files Attached Files
    Last edited by BMV; 01-18-2020 at 06:20 PM.

  6. #6
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    638

    Re: unique count help

    like your last file

    C2=IF(COUNTIF($A$2:$A2,$A2)=1,SUM(IF(FREQUENCY(IF($A$2:$A$100=$A2,IF($B$2:$B$100<>"",MATCH($B$2:$B$100,$B$2:$B$100,0))),ROW($B$2:$B$100)-ROW($B$2)+1),1)),"")

    control+shift+enter
    copy down
    Last edited by CARACALLA; 01-18-2020 at 08:22 PM.

  7. #7
    Registered User
    Join Date
    01-18-2020
    Location
    toronto
    MS-Off Ver
    2016
    Posts
    7

    Re: unique count help

    superb BMV. thanks
    Carcalla its putting the value in every row. BUt thank you so much for responding.

  8. #8
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    638

    Re: unique count help

    I change my formula in post #6

  9. #9
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    487

    Re: unique count help

    Results in columns G and H beginning in G2.

    G2: =A2
    H2: =COUNT(1/FREQUENCY(INDEX(MATCH(B$2:B$12,B$2:B$12,0)*(A$2:A$12=G2),0),ROW(A$2:A$12)-2))-1

    G3: =INDEX(A$2:A$12,MATCH(0,COUNTIF(G$2:G2,A$2:A$12),0))
    H3: =COUNT(1/FREQUENCY(INDEX(MATCH(B$2:B$12,B$2:B$12,0)*(A$2:A$12=G3),0),ROW(A$2:A$12)-2))-1

    These formulas shouldn't require array formula entry. They will return errors when the data has been exhausted.

  10. #10
    Registered User
    Join Date
    01-18-2020
    Location
    toronto
    MS-Off Ver
    2016
    Posts
    7

    Re: unique count help

    hi there
    any way i can keep the 'column C' dynamic.
    I mean if i add new value in column a and b, column c will be auto updated. i tried adding new value in column 13. but that made 'column c' going crazy for both BMv's and Carcalla's formula.
    Is there a solution to that?
    Attached Files Attached Files

  11. #11
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    487

    Re: unique count help

    If there's never be anything below the table beginning in A1,

    G2: =A2
    H2: =COUNT(1/FREQUENCY(MATCH(B$2:INDEX(B:B,COUNTA(B:B)),B$2:INDEX(B:B,COUNTA(B:B)),0)*(A$2:INDEX(A:A,COUNTA(B:B))=G2),ROW($1:$12)-1))-1

    G3: =IFERROR(INDEX(A$2:INDEX(A:A,COUNTA(B:B)),MATCH(0,COUNTIF(G$2:G2,A$2:INDEX(A:A,COUNTA(B:B))),0)),"")
    H3: =IF(G3<>"",COUNT(1/FREQUENCY(MATCH(B$2:INDEX(B:B,COUNTA(B:B)),B$2:INDEX(B:B,COUNTA(B:B)),0)*(A$2:INDEX(A:A,COUNTA(B:B))=G3),ROW($1:$12)-1))-1)

    Select G3:H3 and fill down as far as needed to accommodate expected future entries in cols A and B.

  12. #12
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    638

    Re: unique count help

    C2=IF(COUNTIF($A$2:$A2,$A2)=1,SUM(IF(FREQUENCY(IF($A$2:$A$100=$A2,IF($B$2:$B$100<>"",MATCH($B$2:$B$100,$B$2:$B$100,0))),ROW($B$2:$B$100)-ROW($B$2)+1),1)),"")


    CONTROL+Shift+enter

    copy down

  13. #13
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    965

    Re: unique count help

    Good morning.
    sanybhai, I have marked changing by red
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    01-18-2020
    Location
    toronto
    MS-Off Ver
    2016
    Posts
    7

    Re: unique count help

    Hey CARACALLA THanks.
    Hey BMV thanks. when i copy and paste your formula i i get formula error. I am sure your formula is right. I am doing something wrong. Would you able to send me this in an excel file?
    Attached Images Attached Images

  15. #15
    Valued Forum Contributor
    Join Date
    06-22-2018
    Location
    Blackpool, England
    MS-Off Ver
    2019
    Posts
    408

    Re: unique count help

    Quote Originally Posted by sanybhai View Post
    Hey CARACALLA THanks.
    Hey BMV thanks. when i copy and paste your formula i i get formula error. I am sure your formula is right. I am doing something wrong.
    Just replace the semi-colons (";") with commas (","), should read like this:
    Please Login or Register  to view this content.
    Tim
    Never stop learning!
    <--- please consider *-ing !

  16. #16
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    965

    Re: unique count help

    Quote Originally Posted by harrisonland View Post
    Just replace the semi-colons (";") with commas (","), should read like this:
    Yes. It's regional settings problem. I hoped the changes I have marked by red could be done in the formula.

  17. #17
    Valued Forum Contributor
    Join Date
    06-22-2018
    Location
    Blackpool, England
    MS-Off Ver
    2019
    Posts
    408

    Re: unique count help

    Haha, I assumed it would be... Wouldn't make sense otherwise!

    Tim

  18. #18
    Registered User
    Join Date
    01-18-2020
    Location
    toronto
    MS-Off Ver
    2016
    Posts
    7

    Re: unique count help

    guys any alternate to this problem beside array function. I have about 7000 rows and growing.
    Array function is taking ever to calculate.
    any other formula based solution! Thanks for all your help. learning a lot

  19. #19
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    965

    Re: unique count help

    then helper column have to be created
    =--(COUNTIFS(A$1:A2,A2,B$1:B2,B2)=1)
    and
    =IF(COUNTIF(A$1:A2,A2)=1,SUMIF($A:$A,A2,$E:$E),"")
    where E- helper.
    Attached Files Attached Files
    Last edited by BMV; 01-22-2020 at 12:51 AM.

  20. #20
    Registered User
    Join Date
    01-18-2020
    Location
    toronto
    MS-Off Ver
    2016
    Posts
    7

    Re: unique count help

    Hey BMV
    Can you attach it in a excel file plz

  21. #21
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    965

    Re: unique count help

    Yes, I can. See previous post. And 2nd formula was corrected . There was 2 typos. ";" in stand of "," and $A$:$A$ - $A:$A.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count number of unique values corresponding to unique data in another column
    By STUARTXL in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-26-2019, 12:32 PM
  2. Create a unique list with a count against the unique values
    By barber87 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-12-2017, 10:13 AM
  3. Create unique # of worksheets based on list count with unique names
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-24-2016, 09:51 AM
  4. [SOLVED] Unique Total Value Count per Unique Lookup Values
    By KnightVision in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-02-2014, 05:03 AM
  5. [SOLVED] Count unique entries based off unique criteria in another column
    By hambly in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-11-2014, 03:33 PM
  6. Replies: 0
    Last Post: 03-22-2012, 08:44 PM
  7. Replies: 17
    Last Post: 08-24-2009, 08:58 AM

Tags for this Thread

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