UNIQUE(FILTER) - MULTIPLE INCLUDE

[garypea123]

Good Morning Genius's

Apologies as I cannot download XLSBB

I am having a little issue with my unique filter as I would like to include two columns.

My formula in H3 is =UNIQUE(FILTER(C3:C57,(B3:B57=F3)+(D3:D57=G3)))

However, as you can see there is no JOHN SMITH who has PART 2. However the formula is saying he does.

	FIRST NAME	DESCRIPTION	SURNAME		FIRST NAME	SURNAME	DESCRIPTION
	JOHN	PART 1	SMITH		JOHN	SMITH	PART 1
	JOHN	PART 1	SMITH				PART 2
	JOHN	PART 1	SMITH				PART 4
	PHIL	PART 2	JONES
	KEVIN	PART 2	SMITH
	PHIL	PART 3	JONES
	KEVIN	PART 3	PHILLPS
	JOHN	PART 4	SMITH
	JOHN	PART 4	SMITH
	JOHN	PART 4	SMITH
	JOHN	PART 4	SMITH

 

[garypea123]

Sussed it out

=UNIQUE(FILTER(C3:C57,(B3:B57&D3:D57=F3&G3)))

 

[Peter_SSs]

Sussed it out

I would be wary of concatenating values directly like that - see example below in col H (may be unlikely names but shows what can happen with direct concatenation)
I have shown two safer alternatives. I would use the col I one.

24 02 20.xlsm
	B	C	D	E	F	G	H	I	J
1
2	FIRST NAME	DESCRIPTION	SURNAME		FIRST NAME	SURNAME	DESCRIPTION	DESCRIPTION	DESCRIPTION
3	JO	PART 1	ETON		JO	ETON	PART 1	PART 1	PART 1
4	JOE	PART 3	TON				PART 3
5	JOHN	PART 1	SMITH
6
Sample

Cell Formulas
Range	Formula
H3:H4	H3	=UNIQUE(FILTER(C3:C57,(B3:B57&D3:D57=F3&G3)))
I3	I3	=UNIQUE(FILTER(C3:C57,(B3:B57=F3)*(D3:D57=G3)))
J3	J3	=UNIQUE(FILTER(C3:C57,(B3:B57&"|"&D3:D57=F3&"|"&G3)))
Dynamic array formulas.

 

[garypea123]

Hi Peter - Thank you for your explanation.

I always forget about the * in the formula. If I had used that instead of the + then it would have been perfect (and more safe)

Great response though easily understood.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

Yes, use * for AND conditions, + for OR conditions. You needed AND for this job.