take and sort if

[orsm6]

hi all - how can I expand on Fluff's formula to include to return the same ranges only if column E contains the text "shift 1"?

=TAKE(SORT(HSTACK(i43:i1501,d43:d1501),1,-1),5)

so still looks at top 5 times in column I
matches the times to the reason code in column D

but only gets the top 5 times for the text "shift 1" from column E


TIA

 

[KRice]

In that case, I would probably create the desired arrays using CHOOSECOLS with FILTER, rather than HSTACK:

MrExcel_20240131_C (version 1).xlsx
	D	E	F	G	H	I
37			m	d50.abc
38			k	c
39			j	b
40			i49.xyz	d49.abc
41			i44.xyz	d44.abc
42
43	d43.abc	shift 2				i43.xyz
44	d44.abc	shift 1				i44.xyz
45	d45.abc	shift 1				g
46	a	shift 2				h
47	b	shift 1				j
48	c	shift 1				k
49	d49.abc	shift 1				i49.xyz
50	d50.abc	shift 1				m
51
Sheet4

Cell Formulas
Range	Formula
F37:G41	F37	=TAKE(SORT(CHOOSECOLS(FILTER(D43:I1501,E43:E1501="shift 1"),6,1),1,-1),5)
Dynamic array formulas.

 

[KRice]

I should have offered another variant. The one posted previously filters a larger array to remove undesired rows first, then chooses the columns to keep in the correct order. The second version is closer to the idea presented by @Fluff where the columns are first stacked together in the correct order, and then they are filtered to remove the undesired rows.

MrExcel_20240131_C (version 1).xlsx
	D	E	F	G	H	I
37		m	d50.abc		m	d50.abc
38		k	c		k	c
39		j	b		j	b
40		i49.xyz	d49.abc		i49.xyz	d49.abc
41		i44.xyz	d44.abc		i44.xyz	d44.abc
42
43	d43.abc	shift 2				i43.xyz
44	d44.abc	shift 1				i44.xyz
45	d45.abc	shift 1				g
46	a	shift 2				h
47	b	shift 1				j
48	c	shift 1				k
49	d49.abc	shift 1				i49.xyz
50	d50.abc	shift 1				m
Sheet4

Cell Formulas
Range	Formula
E37:F41	E37	=TAKE(SORT(CHOOSECOLS(FILTER(D43:I1501,E43:E1501="shift 1"),6,1),1,-1),5)
H37:I41	H37	=TAKE(SORT(FILTER(HSTACK(I43:I1501,D43:D1501),E43:E1501="shift 1"),1,-1),5)
Dynamic array formulas.

 

[orsm6]

In that case, I would probably create the desired arrays using CHOOSECOLS with FILTER, rather than HSTACK:

MrExcel_20240131_C (version 1).xlsx
	D	E	F	G	H	I
37			m	d50.abc
38			k	c
39			j	b
40			i49.xyz	d49.abc
41			i44.xyz	d44.abc
42
43	d43.abc	shift 2				i43.xyz
44	d44.abc	shift 1				i44.xyz
45	d45.abc	shift 1				g
46	a	shift 2				h
47	b	shift 1				j
48	c	shift 1				k
49	d49.abc	shift 1				i49.xyz
50	d50.abc	shift 1				m
51
Sheet4

Cell Formulas
Range	Formula
F37:G41	F37	=TAKE(SORT(CHOOSECOLS(FILTER(D43:I1501,E43:E1501="shift 1"),6,1),1,-1),5)
Dynamic array formulas.

thanks KRice this worked.

however.... and I suspect I can't... but need to add criteria to this.

How can i return the same results but also include if Column B = BOSCH

 

[KRice]

How can i return the same results but also include if Column B = BOSCH

Can you clarify this? My example shows data in columns D:I, with a condition on column E having "shift 1". Are you saying that some other column in the range of D:I might or might not have the word "BOSCH"...or are you expanding the range of source data from column A or B to I? And then you only want results that are both "shift 1" AND "BOSCH"?...or do you want results that are either "shift 1" OR "BOSCH"?

 

[orsm6]

Can you clarify this? My example shows data in columns D:I, with a condition on column E having "shift 1". Are you saying that some other column in the range of D:I might or might not have the word "BOSCH"...or are you expanding the range of source data from column A or B to I? And then you only want results that are both "shift 1" AND "BOSCH"?...or do you want results that are either "shift 1" OR "BOSCH"?

That's correct.

I tried expanding the range even to column A, but had no luck

the column containing Bosch is in B... so adding another criteria where it looks at both columns B and E

Hope this image helps

 

[KRice]

okay…and is the condition AND (both must be met) or OR (either condition may be met)?

 

[orsm6]

okay…and is the condition AND (both must be met) or OR (either condition may be met)?

meets both please

 

[KRice]

Thanks...the AND condition (both criteria must be met) is readily achieved by multiplying the two conditional arrays together:

MrExcel_20240131_C (version 1).xlsx
	B	C	D	E	F	G	H	I
37				j	b		j	b
38				i49.xyz	d49.abc		i49.xyz	d49.abc
39				g	d45.abc		g	d45.abc
40
41
42
43	BOSCH		d43.abc	shift 2				i43.xyz
44			d44.abc	shift 1				i44.xyz
45	BOSCH		d45.abc	shift 1				g
46	BOSCH		a	shift 2				h
47	BOSCH		b	shift 1				j
48			c	shift 1				k
49	BOSCH		d49.abc	shift 1				i49.xyz
50			d50.abc	shift 1				m
51
Sheet4

Cell Formulas
Range	Formula
E37:F39	E37	=TAKE(SORT(CHOOSECOLS(FILTER(D43:I1501,(B43:B1501="BOSCH")*(E43:E1501="shift 1")),6,1),1,-1),5)
H37:I39	H37	=TAKE(SORT(FILTER(HSTACK(I43:I1501,D43:D1501),(B43:B1501="BOSCH")*(E43:E1501="shift 1")),1,-1),5)
Dynamic array formulas.

 

[orsm6]

Thanks...the AND condition (both criteria must be met) is readily achieved by multiplying the two conditional arrays together:

MrExcel_20240131_C (version 1).xlsx
	B	C	D	E	F	G	H	I
37				j	b		j	b
38				i49.xyz	d49.abc		i49.xyz	d49.abc
39				g	d45.abc		g	d45.abc
40
41
42
43	BOSCH		d43.abc	shift 2				i43.xyz
44			d44.abc	shift 1				i44.xyz
45	BOSCH		d45.abc	shift 1				g
46	BOSCH		a	shift 2				h
47	BOSCH		b	shift 1				j
48			c	shift 1				k
49	BOSCH		d49.abc	shift 1				i49.xyz
50			d50.abc	shift 1				m
51
Sheet4

Cell Formulas
Range	Formula
E37:F39	E37	=TAKE(SORT(CHOOSECOLS(FILTER(D43:I1501,(B43:B1501="BOSCH")*(E43:E1501="shift 1")),6,1),1,-1),5)
H37:I39	H37	=TAKE(SORT(FILTER(HSTACK(I43:I1501,D43:D1501),(B43:B1501="BOSCH")*(E43:E1501="shift 1")),1,-1),5)
Dynamic array formulas.

Hi KRice - the result for me still returns VALUE... i used the HSTACK formula. the ranges paste just fine, but no value returned