1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count Unique values from a list with conditional filter from another list

Discussion in 'Ask an Excel Question' started by Rediska, Oct 12, 2017 at 2:35 PM.

  1. Rediska

    Rediska Member

    Messages:
    59
    Dear Excellians,

    Please help!
    How can I count unique values in a list with correspondent specific value from column B?

    To count unique cells in column A I use this formula and it works perfectly (referencing to another worksheet):

    {=IFERROR(SUM(IF(FREQUENCY(IF(LEN(INDIRECT("'"&E7&"'!$a$11:$a$8000"))>0,MATCH(INDIRECT("'"&E7&"'!$a$11:$a$8000"),INDIRECT("'"&E7&"'!$a$11:$a$8000"),0),""), IF(LEN(INDIRECT("'"&E7&"'!$a$11:$a$8000"))>0,MATCH(INDIRECT("'"&E7&"'!$a$11:$a$8000"),INDIRECT("'"&E7&"'!$a$11:$a$8000"),0),""))>0,1)),"")}

    How can I modify/add a condition based on a value from another column? (please see attached).

    Thanks a lot!
    Rediska

    Attached Files:

  2. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    370
    Hi, @Rediska!

    Try this array formula (CSE):
    =SUM(N(FREQUENCY(IF(B2:B15="Bunny",MATCH(A2:A15,A2:A15,)),ROW(A2:A15)-ROW(A1))>0))

    Blessings!
    Thomas Kuriakose likes this.
  3. Rediska

    Rediska Member

    Messages:
    59
    Thanks,

    it works fine if on the same sheet, however if I modify for the indirect, modification of the last component ROW(A1) as ROW(NDIRECT("'"&B4&"'!$a$2)))>0)) gives me error.

    =SUM(N(FREQUENCY(IF(INDIRECT("'"&B4&"'!$b$2:$b$1000")="Bunny",MATCH(INDIRECT("'"&B4&"'!$a$2:$a$1000"),INDIRECT("'"&B4&"'!$a$2:$a$1000"),)),ROW(INDIRECT("'"&B4&"'!$a$2:$a$1000"))-ROW(A1))>0))

    What would be the right syntax?

    Thanks!
    Rediska
  4. Rediska

    Rediska Member

    Messages:
    59
    Hi John Jairo V,

    Thanks a lot!
    Question: I removed ROW(A1) from the formula, and it still works fine and gives me the same result.
    It's easier, but why it works?
    Thanks again!
  5. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    370
    Two mistakes:
    1. Put the I in the funcion INDIRECT.
    2. Why you change A1 to A2?

    Correct part is:
    ROW(INDIRECT("'"&B4&"'!A1))

    Is coincidence.
    This part:

    ROW(A2:A15)-ROW(A1)

    Make an array that starts in 1, with 14 elements (size of range).

    ROW(A2:A15) = {2;3;4;5;6;7;8;9;10;11;12;13;14;15}
    ROW(A2:A15) - ROW(A1) = {1;2;3;4;5;6;7;8;9;10;11;12;13;14}


    Blessings!
    Thomas Kuriakose likes this.
  6. Chandrabhan

    Chandrabhan New Member

    Messages:
    1
    Hi,

    How to get the list of all unique cells in column A with a filter "Bunny" from column B?
    A001 Bunny
    A002 Bunny
    A003 Bunny
    A005 Bunny
  7. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    370
    In F2 (CSE):
    =SUM(N(FREQUENCY(IF(B2:B15="Bunny",MATCH(A2:A15,A2:A15,)),ROW(A2:A15)-ROW(A1))>0))

    In D2 (CSE) - And drag it to right and down -:
    =IF(ROWS(D$2:D2)>$F$2,"",INDEX(A$2:A$15,MATCH(,IF($B$2:$B$15="Bunny",COUNTIF(D$1:D1,$A$2:$A$15)),)))

    Check file. Blessings!

    Attached Files:

  8. rahulshewale1

    rahulshewale1 Active Member

    Messages:
    175
    hi @Rediska ,

    please find the attached solution using power query with Easy way.

    Step 1 :
    1.Select Range to A2:B16 Covert to Table (shortcut Key is Ctrl + T)
    2.Go to Power Query Tab >>Select "From Table /range" Option
    3.Power Query Window will Open >>Select Id and Emp name Column
    4.Go to Home tab >>Remove Rows >>Select 4 th option >>Remove Duplicates
    5. Click Close & load connection Only.


    Step 3 :Criteria
    1. Go to Power Query Tab >>Select "Append" Option 3
    2.Select Three or more table Option
    3.Click table 1 >>Add
    4.Click table 2 >>Add
    5.Power Query Window will Open >>Select Criteria Column >>
    Go to tranform Tab >>Fill up
    6.Go Add column Tab >>Conditional Column .Screen shot attached with detail
    7.Then filter only 1
    8.Then remove Criteria and Custom Column
    9. Close & load


    Regard
    Rahul shewale








    Step 2 :Criteria

    Attached Files:

    Thomas Kuriakose likes this.
  9. Rediska

    Rediska Member

    Messages:
    59
    Hi Thomas, thanks!
    Still - this is complete formula, and as soon as I place it in a sheets and add underlined section, formula returns error:

    =SUM(N(FREQUENCY(IF(INDIRECT("'"&B4&"'!$b$2:$b$1000")="Bunny",MATCH(INDIRECT("'"&B4&"'!$a$2:$a$1000"),INDIRECT("'"&B4&"'!$a$2:$a$1000"),)),ROW(INDIRECT("'"&B4&"'!$a$2:$a$1000"))-ROW(INDIRECT("'"&B4&"'!$A$1)))>0))
    I am sure there is a wrong syntax somewhere.

    Any ideas?
    Thanks again,
    Rediska.






  10. Rediska

    Rediska Member

    Messages:
    59
    Thanks a lot Rahul,

    I will definitely use your solution in another project, however in this case I need to implement a formula because file will be a template where data loaded on any worksheet will be automatically sorted and pulled in a single statistic summary worksheet.

    Thanks again,
    Kind regards,
    Rediska
    ------------------------------------------


    please find the attached solution using power query with Easy way.

    Step 1 :
    1.Select Range to A2:B16 Covert to Table (shortcut Key is Ctrl + T)
    2.Go to Power Query Tab >>Select "From Table /range" Option
    3.Power Query Window will Open >>Select Id and Emp name Column
    4.Go to Home tab >>Remove Rows >>Select 4 th option >>Remove Duplicates
    5. Click Close & load connection Only.


    Step 3 :Criteria
    1. Go to Power Query Tab >>Select "Append" Option 3
    2.Select Three or more table Option
    3.Click table 1 >>Add
    4.Click table 2 >>Add
    5.Power Query Window will Open >>Select Criteria Column >>
    Go to tranform Tab >>Fill up
    6.Go Add column Tab >>Conditional Column .Screen shot attached with detail
    7.Then filter only 1
    8.Then remove Criteria and Custom Column
    9. Close & load


    Regard
    Rahul shewale








    Step 2 :Criteria[/quote]
  11. Rediska

    Rediska Member

    Messages:
    59

Share This Page