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.

1. ### RediskaMember

Messages:
61
Dear Excellians,

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

File size:
12.2 KB
Views:
9
2. ### John Jairo VWell-Known Member

Messages:
461
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. ### RediskaMember

Messages:
61
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. ### RediskaMember

Messages:
61
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 VWell-Known Member

Messages:
461
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. ### ChandrabhanNew 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 VWell-Known Member

Messages:
461
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!

File size:
14.1 KB
Views:
9
8. ### rahulshewale1Active Member

Messages:
243
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
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

Regard
Rahul shewale

Step 2 :Criteria

File size:
33.5 KB
Views:
2
9. ### RediskaMember

Messages:
61
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. ### RediskaMember

Messages:
61
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
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

Regard
Rahul shewale

Step 2 :Criteria[/quote]

Messages:
61
12. ### John Jairo VWell-Known Member

Messages:
461
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!
Chirag R Raval likes this.
13. ### RediskaMember

Messages:
61
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!

File size:
15 KB
Views:
4
14. ### NARAYANK991Excel Ninja

Messages:
16,615
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
Chirag R Raval likes this.
15. ### RediskaMember

Messages:
61
Thanks a lot NARAYANK991, it works perfectly!
Of course my syntax was wrong, plus second filter was pointed to a wrong column,

Thanks a lot everyone!!!!!