Finding last cell to contain same value as current cell?

[rcmodelr]

I am trying to set up a spreadsheet where I can track driver arrival times to
figure the individual driver round-trip turn-around time.

Normally, there will be 3 or 4 drivers arriving before same driver arrives
again.
Some cases, however, driver will arrive again with only 1 driver since last
arrival.

=B19-INDIRECT(ADDRESS(ROW(C19)-6+MATCH(C19,C14:C18,0),2)) Works with 4 or 5
drivers hauling from same farm.

I need a formula that will work if only 2 drivers are coming from the farm,
but same formula needs to find correct arrival time if 4 or 5 drivers are
coming from same farm.

 

[JulieD]

Hi

how is the worksheet set up (ie what column has driver name / number) what
column has depart & arrive times?

Cheers
JulieD

 

[rcmodelr]

Column A contains formula in original post to calculate turn-around time
Column B has driver's load arrival time,
Column C has the driver name.

Departure time is NOT tracked. Turn-around time is time difference between
the arrival time of the most recent load and the arrival time of the load
that same driver brought in from the same farm last time he brought a load in.

 

[rcmodelr]

OK, looking through other messages, I found a lookup formula that works...
with ONE EXCEPTION...

Below, I have a fragment from the spreadsheet, with the ticket number & farm
name removed.

Now all I need is a way to set the lookup used in the formula so the start
cell of the lookup range is set to be the FIRST load from that SAME FARM.

Using following formula to find arrival time of last load same driver
brought in:

LOOKUP(2,1/(E1:E9=E10),B1:B9)

set with relative reference so end of range is row immediately above row
formula is in and covering up to 9 rows.

I need to find a way to get the above formula self-modifying so start of
lookup range is no higher than 2 rows up from last blank DRIVER name (column
E) immediately above current farm so turn-around time is only figured on
loads coming from the SAME FARM.

Data shown below IS correct except for having an indicated turn-around time
for a driver who hauled his last load from the previous farm (in which case,
turn-around should be blank since it is his first time coming from the new
farm.


Col. A Col. B Col C Col D Col E

Turn- Arrival Grower/ Driver
Around Time Ticket Trailer Name
7:00 Watkins
7:30 James
1:15 8:15 Watkins (Turn-around = 8:15 - 7:00)
1:15 8:45 James



10:25 Stacy
2:45 11:00 Watkins (Shouldn't have turn-aroound)
3:15 12:00 James (Shouldn't have turn-aroound)
12:35 Jerry
1:40 12:40 Watkins
2:40 1:05 Stacy (Turn-around = 13:05 - 10:25)
1:40 2:15 Jerry
2:10 3:15 Stacy (Turn-around = 3:15 - 1:05)
1:50 4:05 Jerry