Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Unique count based on different cells
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Unique count based on different cells

    I need to get unique value of Suppliers based on the different columns

    Input:
    Functions Suppliers Sites Due Date
    Operations Apple #N/A #N/A
    Operations Orange #N/A 4/13/2020
    End User Services Watermelon #N/A #N/A
    Operations Pineapple #N/A #N/A
    Operations Kiwi Mulgrave 4/13/2018
    Research Fig Mulgrave 4/13/2018
    Research Kiwi Mulgrave 4/13/2018
    Research Kiwi California 4/13/2018
    Research Kiwi California 4/13/2018
    Research Banana #N/A 3/30/2012
    Operations Pear NA 9/15/2019
    Software Services Berry Sydney #N/A
    Software Services Berry Sydney 7/9/2018
    Software Services Grape Sydney 7/9/2018
    Software Services Berry Arizona #N/A
    Software Services Berry Arizona 12/12/2017


    Output:

    2019+ 2018 2017- Not known
    Unique Suppliers


    Condition for output :
    A = Function (Specific), B = Unqiue, C = different, D = date

    For Example : Suppliers unique count when 'Function' is 'Operations' , 'Sites' are different and 'Due Date' as per the column header (2019+, 2018, 2017-, #N/A to be mapped under Not Known.
    Last edited by Sahitya; Jun 4th, 2018 at 03:05 AM.

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,984
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Unique count based on different cells

    not clear to me yet - show us all desired outputs - for operations there are 5 rows - with 5 suppliers - one site and 4 n/a's

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unique count based on different cells

    Quote Originally Posted by oldbrewer View Post
    not clear to me yet - show us all desired outputs - for operations there are 5 rows - with 5 suppliers - one site and 4 n/a's
    Is this fine now?


    Operations
    2019+ 2018 2017- Not known
    Unique Suppliers 2 1 0 2
    Pear
    Orange
    Kiwi Apple
    Watermelon
    Research
    2019+ 2018 2017- Not known
    Unique Suppliers 0 2 1 0
    Kiwi
    Fig
    Banana

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,091
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Unique count based on different cells

    What is "Not known"?
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    New Member
    Join Date
    May 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unique count based on different cells

    Quote Originally Posted by Aladin Akyurek View Post
    What is "Not known"?
    #N/A in the 'Due Date' to be mapped under Not Known.

    it tried Sum -frequency. but it didn't work for me.
    Last edited by Sahitya; Jun 4th, 2018 at 05:18 AM.

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

    Default Re: Unique count based on different cells

    Quote Originally Posted by Sahitya View Post
    #N/A in the 'Due Date' to be mapped under Not Known.

    it tried Sum -frequency. but it didn't work for me.
    ABCDEFGHIJ
    1FunctionsSuppliersSitesDue DateOperations
    2OperationsApple#N/A#N/A2019+20182017-Not known
    3OperationsOrange#N/A4/13/2020Unique Suppliers2102
    4End User ServicesWatermelon#N/A#N/A
    5OperationsPineapple#N/A#N/AOrangeKiwiApple
    6OperationsKiwiMulgrave4/13/2018PearPineapple
    7ResearchFigMulgrave4/13/2018
    8ResearchKiwiMulgrave4/13/2018
    9ResearchKiwiCalifornia4/13/2018
    10ResearchKiwiCalifornia4/13/2018
    11ResearchBanana#N/A3/30/2012
    12OperationsPearNA9/15/2019
    13Software ServicesBerrySydney#N/A
    14Software ServicesBerrySydney7/9/2018
    15Software ServicesGrapeSydney7/9/2018
    16Software ServicesBerryArizona#N/A
    17Software ServicesBerryArizona12/12/2017

    Sheet1




    In G2 control+shift+enter, not just enter:

    =SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)>=2019,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

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

    =IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)>=2019,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")


    In H3 control+shift+enter, not just enter:

    =SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)=2018,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

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

    =IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)=2018,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

    In I3 control+shift+enter, not just enter:

    =SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)<=2017,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

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

    =IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)<=2017,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

    In J3 control+shift+enter, not just enter:

    =SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNA($D$2:$D$17),IF($A$2:$A$17=$F$1,MATCH($B$2:$B$17,$B$2:$B$17,0)))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

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

    =IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNA($D$2:$D$17),IF($A$2:$A$17=$F$1,MATCH($B$2:$B$17,$B$2:$B$17,0)))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

    The same set up also holds for other items like 'reserach'.
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member
    Join Date
    May 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unique count based on different cells

    Quote Originally Posted by Aladin Akyurek View Post
    A B C D E F G H I J
    1 Functions Suppliers Sites Due Date Operations
    2 Operations Apple #N/A #N/A 2019+ 2018 2017- Not known
    3 Operations Orange #N/A 4/13/2020 Unique Suppliers 2 1 0 2
    4 End User Services Watermelon #N/A #N/A
    5 Operations Pineapple #N/A #N/A Orange Kiwi Apple
    6 Operations Kiwi Mulgrave 4/13/2018 Pear Pineapple
    7 Research Fig Mulgrave 4/13/2018
    8 Research Kiwi Mulgrave 4/13/2018
    9 Research Kiwi California 4/13/2018
    10 Research Kiwi California 4/13/2018
    11 Research Banana #N/A 3/30/2012
    12 Operations Pear NA 9/15/2019
    13 Software Services Berry Sydney #N/A
    14 Software Services Berry Sydney 7/9/2018
    15 Software Services Grape Sydney 7/9/2018
    16 Software Services Berry Arizona #N/A
    17 Software Services Berry Arizona 12/12/2017
    Sheet1


    In G2 control+shift+enter, not just enter:

    =SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)>=2019,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

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

    =IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)>=2019,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")


    In H3 control+shift+enter, not just enter:

    =SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)=2018,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

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

    =IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)=2018,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

    In I3 control+shift+enter, not just enter:

    =SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)<=2017,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

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

    =IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNUMBER($D$2:$D$17),IF($A$2:$A$17=$F$1,IF(YEAR($D$2:$D$17)<=2017,MATCH($B$2:$B$17,$B$2:$B$17,0))))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

    In J3 control+shift+enter, not just enter:

    =SUM(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNA($D$2:$D$17),IF($A$2:$A$17=$F$1,MATCH($B$2:$B$17,$B$2:$B$17,0)))),ROW($B$2:$B$17)-ROW($B$2)+1),1))

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

    =IFERROR(INDEX($B$2:$B$17,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$17=""),IF(ISNA($D$2:$D$17),IF($A$2:$A$17=$F$1,MATCH($B$2:$B$17,$B$2:$B$17,0)))),ROW($B$2:$B$17)-ROW($B$2)+1),ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($1:1))),"")

    The same set up also holds for other items like 'reserach'.
    Thanks for the code Aladin. But Sites (Column C) was not considered. If same supplier has two different Sites, then it should be calculated as 2 unique suppliers not as 1.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,091
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Unique count based on different cells

    Quote Originally Posted by Sahitya View Post
    Thanks for the code Aladin. But Sites (Column C) was not considered. If same supplier has two different Sites, then it should be calculated as 2 unique suppliers not as 1.
    Please do not quote the whole reply.

    The formulas do deliver the output you specified.


    And if sites must be taken into account, what must happen when a site is indicated as #N/A?
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    New Member
    Join Date
    May 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unique count based on different cells

    Quote Originally Posted by Aladin Akyurek View Post
    Please do not quote the whole reply.

    The formulas do deliver the output you specified.


    And if sites must be taken into account, what must happen when a site is indicated as #N/A?

    It will be considered as one site.

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,091
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Unique count based on different cells

    Quote Originally Posted by Sahitya View Post
    It will be considered as one site.
    Try to be precise:

    X,Y
    X,#N/A

    Is this 1 or 2?
    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
  •