CODEWITHSUNDEEP
google-sheets
anjaryes
anjaryes

Reputation: 111

How to Use UNIQUE() Function for Filtered Rows in Google Spreadsheet

I have a column in Google spreadsheet consist of names, and I already apply filter function in the header.So, I already able to choose filter option based on the name in the column.
What I want to do is get value of cell that has been filtered without any duplication using UNIQUE() function but only for visible rows only. 

+----------+----------+                              +---------+---------+
|   name  v|   Col2  v|   result table if i apply    |  name  v|  Col 2 v|
+----------+----------+ >> filter by name "John">>   +---------+---------+
| John     |    a     |                              | John    |    a    |
| andi     |    b     |                              | John    |    v    |
| John     |    v     |                              | John    |    d    |
| John     |    d     |                              | John    |    r    |  
| andi     |    f     |                              +---------+---------+
| John     |    r     |
+----------+----------+

If I use conventional UNIQUE() function to column 'name' of the filtered table, it will still show "John" and "andi" respectively, instead of only "John" showed up, because the hidden filtered rows of "andi" still being processed by UNIQUE() function.

Please help me to make UNIQUE() function only process filtered rows, so the result will be only "JOHN". To put it simple, I want UNIQUE() has the ability as SUBTOTAL() which is only process/calculate the active or filtered cell/rows/range.

Thank you for any help.

Upvotes: 1

Views: 4822

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18717

Functions are applied to whole ranges, you may try filter data with function:

=FILTER(A:B,A:A="John")

or with query:

=QUERY({A:B},"select 'John', Col2 where Col1 = 'John' label 'John' 'Name', Col2 'Col2'")

And then apply unique:

=UNIQUE(FILTER(A:B,A:A="John"))

Upvotes: 1

Related Questions