#=SORT(UNIQUE(FILTER(A2:E10,{0,1,1,0,1})))

[ellison]

Hi, we are trying to work with a unique filter like this: =SORT(UNIQUE(FILTER(A2:E10,{0,1,1,0,1})))

It's great, but we can't figure out how to add in:
i) a filter for Info-1 = TRUE
ii) sort the info by Info-2
iii) (we're hoping the dedupe is already being taken care of by the formula!)

Hope this makes things a little clearer:

Excel-Filter-unique-sort-one-criteria.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Row	Info-1	Info-2	Info-3	Info-4		Formula in I2 in trying:		Info-1	Info-2	Info-4
2	2	TRUE	Beta		WIP		1: To only include fields for Info-1, Info-2 and Info-4, "done"		FALSE	Beta	WIP
3	3		Beta	somteimes	WIP		2: To dedupe those 3 fields, "TBC"		FALSE	Foxtrot	volume
4	4	FALSE	Beta	somteimes	WIP		3: Include only entries where Info-1 is TRUE, "failed"		TRUE	Beta	WIP
5	5	TRUE	Beta	somteimes	WIP		4: To sort by Info-2, "failed"		TRUE	Foxtrot	volume
6	6	TRUE	Beta	somteimes	WIP				TRUE	Alpha	obsolete
7	7		Foxtrot	somteimes	obsolete				0	Beta	WIP
8	8	TRUE	Foxtrot		volume				0	Foxtrot	obsolete
9	9	TRUE	Alpha	cells	obsolete
10	10	FALSE	Foxtrot	blank	volume
Sheet1

Cell Formulas
Range	Formula
I2:K8	I2	=SORT(UNIQUE(FILTER(A2:E10,{0,1,1,0,1})))
Dynamic array formulas.

Huge thanks for taking a look!

 

[Fluff]

How about

=SORT(UNIQUE(FILTER(CHOOSECOLS(B2:E10,1,2,4),B2:B10)),2)

 

[ellison]

Wow, that works brilliantly - I'm just trying to break it down.....

=SORT(UNIQUE(FILTER(CHOOSECOLS(B2:E10,1,2,4),B2:B10)),2)

- choose cols chooses which columns from the range to add, if you skip a number then the column isn't added
- and I think the last "2" in the formula determines which field that the newly reorganised data will be sorted by
- and I'm stumped as to how the formula knows to only include "TRUE" from Info-1?!

 

[Fluff]

You could write the formula like

=SORT(UNIQUE(FILTER(CHOOSECOLS(B2:E10,1,2,4),B2:B10=TRUE)),2)

but as you already have true in the cells you can just use the range.

 

[ellison]

absolutely amazing, huge thanks (again!!)

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ellison]

Hi, I was wondering if there is way of getting this formula to return a blank cell (rather than return a zero)...
i.e. for blank cells in columns for Info-2 and Info-4 such as D2, E5 and E7

Excel-Filter-unique-sort-one-criteria.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Row	Info-1	Info-2	Info-3	Info-4		done via current formula	Info-1	Info-2	Info-4		done manually to try to show what we are after:	Info-1	Info-2	Info-4
2	2	TRUE			WIP			TRUE	Alpha	obsolete			TRUE	Alpha	obsolete
3	3		Beta	somteimes	WIP			TRUE	Beta	0			TRUE	Beta
4	4	FALSE	Beta	somteimes	WIP			TRUE	Beta	WIP			TRUE	Beta	WIP
5	5	TRUE	Beta	somteimes				TRUE	Foxtrot	0			TRUE	Foxtrot
6	6	TRUE	Beta	somteimes	WIP			TRUE	0	WIP			TRUE		WIP
7	7		Foxtrot	somteimes	obsolete
8	8	TRUE	Foxtrot
9	9	TRUE	Alpha	cells	obsolete
10	10	FALSE	Foxtrot	blank	volume
2nd-Q-blanks

Cell Formulas
Range	Formula
H2:J6	H2	=SORT(UNIQUE(FILTER(CHOOSECOLS(B2:E10,1,2,4),B2:B10=TRUE)),2)
Dynamic array formulas.

Thanks for taking a peek!

 

[Fluff]

How about

=LET(x,SORT(UNIQUE(FILTER(CHOOSECOLS(B2:E10,1,2,4),B2:B10=TRUE)),2),IF(x="","",x))

 

[ellison]

Wow, that is really helpful - HUGE thanks
All the best

 

[Fluff]

Glad to help & thanks for the feedback.