Position of the most recent X

[Howlin]

Hi,

We like to maintain one of our machines by the number of rolls that have passed through it. We record this on Excel and I get the operators to record X in the service clean box when the machine has been cleaned.

=24-(LOOKUP(2,1/(Table1[WO]<>""),ROW(Table1[WO]))-LOOKUP(2,1/(Table1[Service Clean]<>""),ROW(Table1[Service Clean])))

This then tells us how long until the 24 service clean(the reason for 24 at the start of the formula.) The above formula works well when the operators fill in the table down. However for some reason on the next line the operators fill the table by inserting a new line at the top. I'm getting resistance to changing that and there is some historic macros that would need to be changed so I want to avoid this at all costs.

Is there a way to change the formula so that it works in reverse. It looks for the position of the first X in the column. In the example it is cell D7. Then counts upwards to row B. So it informs me with done 5 rolls since the last clean?

Cheers,

Tom

[Richard Buttrey]

Hi,

Maybe

Please Login or Register  to view this content.

[Howlin]

=MATCH("x",D:D)-ROW($A$1)-1

Match finds the position of the last X, so will always find the oldest clean.

I could filter by Row A1:A30 but that seems like if we went over 30 it wouldn't display correctly. However, I know if it didn't display they haven't cleaned in the last 30. IF I did an if at this point to display clean is over due that might work.

[Richard Buttrey]

Hi,

So what are you saying?

Do you mean with several x's in the column you want to find the number of rows between the last x and the penultimate x?

[Howlin]

So as the spreadsheet is used there become more and more X's in Column D.

I'd like to no when the first X is It's position away from row 2. So the number of rows between. THere should be a X in column B unless they have cleaned after that roll.

[Richard Buttrey]

Hi,

Would you offer a few examples please. I'm struggling to understand your exact requirement.

Add different sets of x's and for each set indicate the value you want the formula to return.

[Howlin]

I've created 3 examples in the attached sheetExcel query 5.xlsx

To show you what I want to count. I also coloured in yellow the rolls after the clean and noted in the number it should be. Hope it is clearer now.

[Richard Buttrey]

Hi,

In that case it just needs a 'False' argument in the original formula I gave.

Please Login or Register  to view this content.

[Howlin]

Perfect thanks!

[Richard Buttrey]

My pleasure and thanks for the rep.