Getting excel to choose from multiple matches based on criteria

**bmancell** · 06-07-2010, 07:40 PM

A worksheet I am dealing with has the following:

A1:A2000 -> 4 digit ID number
B1:B2000 -> Calendar shift date
C1:C2000 -> Shift Ident, 1 = dayshift, 2 = nightshift.
D1:D2000 -> Start timestamp
E1:E2000 -> End Timestamp

The data applies to start and end timestamps of shift change times at a mine site for haul trucks and other mine equipment. The computer system that records the data starts its shift change at 7:00 AM and 7:00 PM each day. The "Start Timestamp" is when the operator left their truck at the end of the shift, and the "End Timestamp" is when the operator gets on their truck. The problem is that in the middle of that time slot, the computer does its shift change, adding another start and end timestamp to the system, so a shift change that started at 6:40 AM, and finished at 7:24 AM shows in 2 entries:
Start timestamp of 6:40 AM, End timestamp of 7:00 AM, and another Start Timestamp of 7:00 AM, followed by an End timestamp of 7:24 AM. This creates 2 entries to sort through. The shift date changes over at 7:00 AM every day.

I am trying to write 2 formulas, one to give me the shift start time, or time after 7:00 AM or 7:00 PM (depending on the shift ident), the other to give me the end shift time, or time before 7:00 AM or 7:00 PM, again depending on the shift ident.

Here is a short sample of my data, and what I am trying to output. Keep in mind this is only a small sample, The data that comes in is unsorted, and in thousands of rows.

Data is as follows:
"Sheet1"
A1=2431, B1=05/1/2010 0:00, C1=1, D1=7:00, E1=7:24
A2=2431, B2=05/1/2010 0:00, C1=1, D1=18:40 E2=19:00

"Sheet2"
A1=2431, B1=05/1/2010 0:00, C1=1

And I am trying to calculate D1, and E1 on Sheet2. D1 is start time, E1 is end time.

I enter in the following formula into Sheet2, cell D1:
=INDEX(Sheet1!D1:D2,MATCH(Sheet2!A1&Sheet2!B1&Sheet2!C1,Sheet1!A1:A2&Sheet1!B1:B2&Sheet1!C1:C2,0))

The expected result is 7:24 and I get 7:24

Then I enter the following formula into Sheet2, cell E1:
=INDEX(Sheet1!E1:E2,MATCH(Sheet2!A1&Sheet2!B1&Sheet2!C1,Sheet1!A1:A2&Sheet1!B1:B2&Sheet1!C1:C2,0))

The expected result is 18:40, I get 7:00.

So the index and match is giving me the first result it comes to. I can create a formula to make the 7:00 and 19:00 cells blank, but then the formula just gives me 0:00 instead of the 18:40 that I want. Is there a way to say "if(ISBLANK...." or another way perhaps? I have spent a couple of days on this now, and am lost.

Any help on this would be much appreciated.

**DonkeyOte** · 06-08-2010, 03:28 AM

Your sample values, formulas and suggested results don't tie out - the Arrays would return 07:00 and 07:24 respectively - presumably you have the column references mixed up (ie first should reference E and the second D ?)

It's unclear how your data (sheet1) is sorted - for sake of demo we'll make no assumptions and presume unsorted.

First, to avoid the Array requirement you should really add the concatenation as a field on Sheet1, eg:

Sheet1!F1:
=A1&"@"&B1&"@"&C1
copied down

You can then just use:

Sheet2!E1:
=INDEX(Sheet1!$E$1:$E$2,MATCH($A1&"@"&$B1&"@"&$C1,Sheet1!$F$1:$F$2,0))
confirmed with Enter

Sheet2!F1:
=LOOKUP(2,1/(Sheet1!$F$1:$F$2=$A1&"@"&$B1&"@"&$C1),Sheet1!$D$1:$D$2)
confirmed with Enter

(regards the "no assumptions" - we do still assume that the 2nd half of the "transaction" is always to be listed after the first)

**bmancell** · 06-08-2010, 03:19 PM

Unfortunately, my data is not in any sort of order as assumed in your last line. The formula you gave me appears to work for most of the data, but misses a few.

I have attached a part of the file I'm working on, containing the data as it appears from the database when pulled into excel, along with your formulas entered in, and I have highlighted in red the cell values that are missing. Some trucks do not get anything in the start and end times because they may have started up part way through the day, thus no shift change status existed in the morning (or evening). Others were down for maintenance, and do not have any status changes for shift change.

Any help on this would be much appreciated!

Thankyou

**DonkeyOte** · 06-08-2010, 04:28 PM

Not sure if I've understood or not but perhaps try reverting to a SUMIF based approach given the inconsistent ordering of "shift transactions", ie:

D2:
=SUMIF(Shift_Start_End!$I$6:$I$2000,$A2&"@"&$B2&"@"&$C2,Shift_Start_End!G$6:G$2000)
copied across matrix D2:E19

Apply a Custom Format to the matrix of: hh:mm:ss;;;
(note above format assumes midnight will never be a valid time result)

EDIT: I suspect that this will not in fact work in all cases (ie where you have >1 start / end time for any given Key (Column I))

**bmancell** · 06-14-2010, 09:26 AM

Worked great! Thanks! I did have to fix the data so that there was never more than one start and one end for each date, but the formula worked perfect.