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]

You still have not run the test I've asked about...to confirm that the formula-delivered "BOSCH" actually can match to a manually entered bosch. I would recommend de-constructing the formula to confirm that each part delivers something expected:
Your formula and the main data table are on the same worksheet, correct? In an empty column to the right of your data table, in row 43, enter this formula

=B43:B1501="bosch"

...what do you see?
Beside that formula, enter this:

=E43:E1501="shift 1"

Beside that, enter this:

=(B43:B1501="bosch")*(E43:E1501="shift 1")

...do you see a list of 1's and 0's and nothing else? And do the 1's correspond to the rows that you want?
To the right of those formula, on row 43, enter:

HSTACK(I43:I1501,D43:D1501)

...do you see the I and D column contents spill correctly?
Any issues with any of the results as you scroll down the sheet?

 

[orsm6]

You still have not run the test I've asked about...to confirm that the formula-delivered "BOSCH" actually can match to a manually entered bosch. I would recommend de-constructing the formula to confirm that each part delivers something expected:
Your formula and the main data table are on the same worksheet, correct? In an empty column to the right of your data table, in row 43, enter this formula

=B43:B1501="bosch"

...what do you see?
Beside that formula, enter this:

=E43:E1501="shift 1"

Beside that, enter this:

=(B43:B1501="bosch")*(E43:E1501="shift 1")

...do you see a list of 1's and 0's and nothing else? And do the 1's correspond to the rows that you want?
To the right of those formula, on row 43, enter:

HSTACK(I43:I1501,D43:D1501)

...do you see the I and D column contents spill correctly?
Any issues with any of the results as you scroll down the sheet?

i do very much appreciate your help.

I don't fully understand what you're looking for, but i have managed to get the results i needed by using column C. So I have still used your formula that you gave me.

many thanks again.

 

[KRice]

You're welcome...glad you have it working.