Filter with include condition is unique values

[ExcelNewbie2020]

Let say i need to filter range column A:D. for my include condition should be Unique values from column C..

if we apply that in formula (that doesn't work)
= FILTER(A:D,Unique(C:C))

What could be the correct formula?

Many Thanks

 

[Burrgogi]

Read method #5 from this tutorial.

How to Filter Unique Values in Excel (8 Easy Ways) - ExcelDemy

We use the UNIQUE, FILTER, MATCH, and INDEX functions, Excel features as well as VBA Macro code to filter unique in Excel.

www.exceldemy.com

 

[etaf]

can you give an example as the row - will NOT be unique
for example
ROW 1 and ROW 4 has the same entry in C - 1

Book4
	A	B	C	D
1	a	1	1	s
2	bb	2	2	s
3	c	3	12	s
4	d	4	1	s
Sheet1

BUT row 1 and row 4 is NOT unique in all the entries
so which ROW do you want to retain out of row 1 or row4, and NOT sure how to do that for excel to know

i think this is why you are getting value errors, i'm too and cant see a why around it

BUT if all the entries in row 1 and 4 cells where the same, so the row is duplicated , then you can filter OUT the duplicates

=UNIQUE(FILTER(A1:D4,A1:A4<>""))

BUT that removes ROW1 and ROW4

Book4
	A	B	C	D	E	F	G	H	I	J	K
1	a	1	1	s				a	1	1	s
2	bb	2	2	s				bb	2	2	s
3	c	3	12	s				c	3	12	s
4	a	1	1	s
Sheet1

Cell Formulas
Range	Formula
H1:K3	H1	=UNIQUE(FILTER(A1:D4,A1:A4<>""))
Dynamic array formulas.

I dont know how to filter based on column C
you can remove both the rows for duplicates based on column C

so ROW1 and ROW4 would not appear in the filtered data at all

=UNIQUE(FILTER(A1:D4,(COUNTIF(C1:C4,C1:C4)=1)))

Book4
	A	B	C	D	E	F	G	H	I	J	K
1	a	1	1	s				bb	2	2	s
2	bb	2	2	s				c	3	12	s
3	c	3	12	s
4	d	4	1	s
Sheet1

Cell Formulas
Range	Formula
H1:K2	H1	=UNIQUE(FILTER(A1:D4,(COUNTIF(C1:C4,C1:C4)=1)))
Dynamic array formulas.

 

[ExcelNewbie2020]

can you give an example as the row - will NOT be unique
for example
ROW 1 and ROW 4 has the same entry in C - 1

Book4
	A	B	C	D
1	a	1	1	s
2	bb	2	2	s
3	c	3	12	s
4	d	4	1	s
Sheet1

BUT row 1 and row 4 is NOT unique in all the entries
so which ROW do you want to retain out of row 1 or row4, and NOT sure how to do that for excel to know

i think this is why you are getting value errors, i'm too and cant see a why around it

BUT if all the entries in row 1 and 4 cells where the same, so the row is duplicated , then you can filter OUT the duplicates

=UNIQUE(FILTER(A1:D4,A1:A4<>""))

BUT that removes ROW1 and ROW4

Book4
	A	B	C	D	E	F	G	H	I	J	K
1	a	1	1	s				a	1	1	s
2	bb	2	2	s				bb	2	2	s
3	c	3	12	s				c	3	12	s
4	a	1	1	s
Sheet1

Cell Formulas
Range	Formula
H1:K3	H1	=UNIQUE(FILTER(A1:D4,A1:A4<>""))
Dynamic array formulas.

I dont know how to filter based on column C
you can remove both the rows for duplicates based on column C

so ROW1 and ROW4 would not appear in the filtered data at all

=UNIQUE(FILTER(A1:D4,(COUNTIF(C1:C4,C1:C4)=1)))

Book4
	A	B	C	D	E	F	G	H	I	J	K
1	a	1	1	s				bb	2	2	s
2	bb	2	2	s				c	3	12	s
3	c	3	12	s
4	d	4	1	s
Sheet1

Cell Formulas
Range	Formula
H1:K2	H1	=UNIQUE(FILTER(A1:D4,(COUNTIF(C1:C4,C1:C4)=1)))
Dynamic array formulas.

Thanks man.. for the row1 and 4. Either 1 of them should appear because in my row values they are duplicate.
(A1:D1 and A4:D4 are duplicate)

 

[etaf]

ok, so as shown above then

=UNIQUE(FILTER(A1:D4,A1:A4<>""))
I hve put a range , as referring to just the column is over 1million rows and can slow things down
just make it range you want exceed

hence the <>"" so it only filters rows with data in column A
=UNIQUE(FILTER(A1:D1000,A1:A1000<>""))