Results 1 to 9 of 9

Thread: Count uniques per column value

  1. #1
    New Member
    Join Date
    Dec 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Count uniques per column value

    I have a large one year dataset (around 20,000 rows) where there are two main columns, one is the unique identifier (duplicated), the other is the employee assigned to that unique identifier (unique).

    I am trying to develop a formula to count the number of unique employees assigned to each identifier, as an example below. Columns A-B is the data. Columns D-E is what I want the output to look like. So far I have tried some SUM and FREQUENCY, none that seem to work seamlessly or perhaps I am making the formula too complicated for what I need to do.

    Thank you very much for your knowledge.

    Excel 2013/2016
    ABCDE
    1ProjectEmployeeProjectEmployees
    22017-00011411 - John2017-00011
    32017-00021411 - John2017-00022
    42017-0002212 - David2017-00031
    52017-00031411 - John2017-00041
    62017-00041411 - John2017-00051
    72017-00051411 - John2017-00061
    82017-00061411 - John2017-00071
    92017-00071411 - John2017-00081
    102017-00081411 - John2017-00093
    112017-00091411 - John2017-00101
    122017-0009212 - David2017-00111
    132017-0009344 - Jason2017-00121
    142017-00101411 - John2017-00131
    152017-00111411 - John2017-00141
    162017-00121411 - John2017-00152
    172017-00131411 - John
    182017-00141411 - John
    192017-00151411 - John
    202017-00153773 - Charles

    Data Sheet




  2. #2
    Board Regular rpaulson's Avatar
    Join Date
    Oct 2007
    Location
    Fremont, OH USA
    Posts
    964
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count uniques per column value

    if i undertand correctly then, this should work in cell E2
    =COUNTIF(A:A,D2)
    fill down as needed

    hth,
    Ross
    Last edited by rpaulson; Feb 19th, 2018 at 01:50 PM.

  3. #3
    Board Regular
    Join Date
    Oct 2011
    Posts
    3,728
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Count uniques per column value

    Try:
    Assumes there are no blank columns in the Employee field.
    This is an array formula and must be entered with CTRL-SHIFT-ENTER.
    Drag formula down rows as needed.

     ABCDE
    1ProjectEmployee ProjectEmployees
    22017-00011411 - John 2017-00011
    32017-00021411 - John 2017-00022
    42017-0002212 - David 2017-00031
    52017-00031411 - John 2017-00041
    62017-00041411 - John 2017-00051
    72017-00051411 - John 2017-00061
    82017-00061411 - John 2017-00071
    92017-00071411 - John 2017-00081
    102017-00081411 - John 2017-00093
    112017-00091411 - John 2017-00101
    122017-0009212 - David 2017-00111
    132017-0009344 - Jason 2017-00121
    142017-00101411 - John 2017-00131
    152017-00111411 - John 2017-00141
    162017-00121411 - John 2017-00152
    172017-00131411 - John   
    182017-00141411 - John   
    192017-00151411 - John   
    202017-00153773 - Charles   

    Spreadsheet Formulas
    CellFormula
    E2{=SUM(IF(FREQUENCY(IF($A$2:$A$20=$D2,MATCH($B$2:$B$20,$B$2:$B$20,0)),ROW($B$2:$B$20)-ROW($B$2)+1),1))}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,438
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Count uniques per column value

    In E2 control+shift+enter, not just enter, and copy down:

    =SUM(IF(FREQUENCY(IF($A$2:$A$20=$D2,IF(1-($B$2:$B$20=""),MATCH($B$2:$B$20,$B$2:$B$20,0))),ROW($B$2:$B$20)-ROW($B$2)+1),1))
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    Board Regular
    Join Date
    Oct 2011
    Posts
    3,728
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Count uniques per column value

    @Aladin - in your formula is there an advantage to using 1-($B$2:$B$20="") vs $B$2:$B$20 < > ""

  6. #6
    New Member
    Join Date
    Dec 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count uniques per column value

    Thank you, @rpaulson @AhoyNC @Aladin Akyurek
    All three formulas worked in the data sheet - Over hindsight, I did not realize it was possible to do @rpaulson 's method without reading the field with employee names, but just count for duplicated entries. It can be made to work since since all row possibilities can be deduped to uniques (e.g. no two rows were the same).

    @AhoyNC and @Aladin Akyurek methods read through all the data, looked at duplicates and generated the count of employees based on the actual sum of matches per each identifier. This process took some time for the machine to parse the formula (not quick with ~20K rows!)

    I have learned new formulas from this topic and appreciate the insight it has given me. Thank you, kind benefactors.
    Last edited by anonemous; Feb 19th, 2018 at 05:09 PM.

  7. #7
    Board Regular
    Join Date
    Oct 2011
    Posts
    3,728
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Count uniques per column value

    You're welcome the formulas that Aladin & I gave would take into account if an employee name was duplicated on a project. Aladin's also took into account if there was a blank cell.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,438
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Count uniques per column value

    Quote Originally Posted by AhoyNC View Post
    @Aladin - in your formula is there an advantage to using 1-($B$2:$B$20="") vs $B$2:$B$20 < > ""
    No.
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,438
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Count uniques per column value

    Quote Originally Posted by anonemous View Post
    [...]
    Over hindsight, I did not realize it was possible to do @rpaulson 's method without reading the field with employee names, but just count for duplicated entries. It can be made to work since since all row possibilities can be deduped to uniques (e.g. no two rows were the same).
    [...]
    If no employee is associated with a project more than once, the COUNTIF solution is good enough...
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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