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]

Can you post the formula you are using?...a direct copy/paste into your post.
Also, confirm the columns where BOSCH and shift 1 might appear, and the columns that should appear in the final result.

 

[orsm6]

Can you post the formula you are using?...a direct copy/paste into your post.
Also, confirm the columns where BOSCH and shift 1 might appear, and the columns that should appear in the final result.

=TAKE(SORT(FILTER(HSTACK(I43:I1501,D43:D1501),(B43:B1501="BOSCH")*(E43:E1501="shift 1")),1,-1),5)

the word Bosch is the result of a formula to get the word from another column.
Bosch appears in B
Shift 1 appears in E

 

[KRice]

hmmm...I don't see any issues with it. A #VALUE! error? Is there anything strange in rows 43:1501...or just a table of values?

If you eliminate *(E43:E1501="shift 1") from the expression, do you get results?
If you eliminate (B43:B1501="BOSCH")* from the expression, do you get results?
Also, type Bosch somewhere on your worksheet, as I've done in B38, then enter an expression similar to that shown in B39, but reference a known "Bosch" value in column B and see if the result is TRUE.

MrExcel_20240131_C (version 1).xlsx
	B
38	Bosch
39	TRUE
40
41
42
43	BOSCH
Sheet4

Cell Formulas
Range	Formula
B39	B39	=B38=B43

 

[orsm6]

hmmm...I don't see any issues with it. A #VALUE! error? Is there anything strange in rows 43:1501...or just a table of values?

If you eliminate *(E43:E1501="shift 1") from the expression, do you get results?
If you eliminate (B43:B1501="BOSCH")* from the expression, do you get results?
Also, type Bosch somewhere on your worksheet, as I've done in B38, then enter an expression similar to that shown in B39, but reference a known "Bosch" value in column B and see if the result is TRUE.

MrExcel_20240131_C (version 1).xlsx
	B
38	Bosch
39	TRUE
40
41
42
43	BOSCH
Sheet4

Cell Formulas
Range	Formula
B39	B39	=B38=B43

I get values when i remove Bosch

as mentioned, the column where it finds bosch has a formula that is returning the word bosch. i think i can work around this though with another column.

 

[orsm6]

hmmm...I don't see any issues with it. A #VALUE! error? Is there anything strange in rows 43:1501...or just a table of values?

If you eliminate *(E43:E1501="shift 1") from the expression, do you get results?
If you eliminate (B43:B1501="BOSCH")* from the expression, do you get results?
Also, type Bosch somewhere on your worksheet, as I've done in B38, then enter an expression similar to that shown in B39, but reference a known "Bosch" value in column B and see if the result is TRUE.

MrExcel_20240131_C (version 1).xlsx
	B
38	Bosch
39	TRUE
40
41
42
43	BOSCH
Sheet4

Cell Formulas
Range	Formula
B39	B39	=B38=B43

thanks heaps for your help. I moved the range from B to C where it holds the name Bosch Filler.... this will work fine for me

 

[KRice]

It should not matter if the word Bosch is being delivered by a formula, but the formula may be delivering something other than just b-o-s-c-h. There may be a space or other character. That's why I was interesting in seeing a logical test. Physically type bosch in a cell, and then perform an equality test where you see if the just-typed bosch equals the formula-delivered bosch.

 

[orsm6]

It should not matter if the word Bosch is being delivered by a formula, but the formula may be delivering something other than just b-o-s-c-h. There may be a space or other character. That's why I was interesting in seeing a logical test. Physically type bosch in a cell, and then perform an equality test where you see if the just-typed bosch equals the formula-delivered bosch.

i did copy the word and paste it as a value to check for a space, there wasn't. but will look again

 

[orsm6]

It should not matter if the word Bosch is being delivered by a formula, but the formula may be delivering something other than just b-o-s-c-h. There may be a space or other character. That's why I was interesting in seeing a logical test. Physically type bosch in a cell, and then perform an equality test where you see if the just-typed bosch equals the formula-delivered bosch.

sorry i aslo physically typed BOSCH into a cell in column B against shift 1 - still not working.

 

[KRice]

Okay…and the other test mentioned in post 13… if you type bosch into some other cell that is not part of the B range in the formula (let’s say that happens to be cell C30)…if you then find an empty cell and enter the formula =C30=Bxx, where Bxx represents the cell address a formula-delivered “bosch”, what do you get as a result?,,,True or False?

 

[orsm6]

Okay…and the other test mentioned in post 13… if you type bosch into some other cell that is not part of the B range in the formula (let’s say that happens to be cell C30)…if you then find an empty cell and enter the formula =C30=Bxx, where Bxx represents the cell address a formula-delivered “bosch”, what do you get as a result?,,,True or False?

here are the two variations of the formula used:

=TAKE(SORT(FILTER(HSTACK(I43:I1501,D43:D1501),(B43:B1501="Bosch")*(E43:E1501="shift 1")),1,-1),5) hard-coded the word "Bosch" = resulted in #VALUE!
=TAKE(SORT(FILTER(HSTACK(I43:I1501,D43:D1501),(B43:B1501=N42)*(E43:E1501="shift 1")),1,-1),5): where the word Bosch is picked up from cell reference = resulted in #VALUE!

refer to snip: hardcoded.png

further, i noticed that when i deleted the word BOSCH from the formula the range box for col E (shift name) disappeared... not sure what is up with that. see snip huh.png