• 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...

  • 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

Rediska

Member
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
 

Attachments

  • Sample1.xlsx
    12.2 KB · Views: 9
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
 
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!
 
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.

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

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

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.

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!
 
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
 
How to get the list of all unique cells in column A with a filter "Bunny" from column B?

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!
 

Attachments

  • Sample1.xlsx
    14.1 KB · Views: 10
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
 

Attachments

  • Sample1.xlsm
    33.5 KB · Views: 2
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.






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!
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!
 
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]
 
Hi John, 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.
 
Hi again, @Rediska!

The formula must be:
=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))

Or (you can erase the "$", because is a text into indirect function):
=SUM(N(FREQUENCY(IF(INDIRECT("'"&B4&"'!b2:b1000")="Bunny",MATCH(INDIRECT("'"&B4&"'!a2:a1000"),INDIRECT("'"&B4&"'!a2:a1000"),)),ROW(INDIRECT("'"&B4&"'!a2:a1000"))-ROW(INDIRECT("'"&B4&"'!a1")))>0))

The mistake was the blue double quotes.

Blessings!
 
Thanks a lot John, it works perfectly now!!!

Sorry, but.... another question: how can I add additional filter from another column based on it's value?

I've tried as a second filter, and as combining two in one filter with "and", but I guess I my syntax is wrong again.

Could you please look at the attached, sheet 1 is data and sheet 2 solutions?
Thanks so much!
 

Attachments

  • Sample2.xlsx
    15 KB · Views: 4
Hi ,

Does this not work ?

=SUM(N(FREQUENCY(IF(INDIRECT("'"&A5&"'!c2:c1000")="Bunny",IF(INDIRECT("'"&A5&"'!B2:B1000")<10,MATCH(INDIRECT("'"&A5&"'!a2:a1000"),INDIRECT("'"&A5&"'!a2:a1000"),))),ROW(INDIRECT("'"&A5&"'!a2:a1000"))-ROW(INDIRECT("'"&A5&"'!a1")))>0))

where the portion colored in red is the second filter added.

Narayan
 
Back
Top