Identify the pair and calculate the time difference if certain criteria match

Hi Guys,


I need to identify pairs and calculate the time difference between two date-times provided:
1) It belongs to the same customer
2) The two date-time's difference is not beyond +/- 10 seconds

The pairing is done as following:
I1-T1 or T1-I1
I2-T2 or T2-I2
I3-T3 or T3-I3
I4-T4 or T4-I4
I5-T5 or T5-I5
I6-T6 or T6-I6


Where ever these conditions don't meet, it should be blank.

Example INPUT data:

Cust Date Category Expected O/P
A 8/30/2014 8:46:04 I2
A 8/30/2014 8:46:05 I1 -1.0001042
A 8/30/2014 8:46:07 I4
A 8/30/2014 8:46:14 T1 1.0001042
A 9/1/2014 9:00:01 T2
B 8/16/2014 16:25:15 T3 -0.0000116
B 8/16/2014 16:25:16 I3 0.0000116
B 8/16/2014 16:24:34 T4
C 10/1/2014 14:20:14 I4
C 10/1/2014 14:20:18 I5
C 10/2/2014 12:12:16 T5
C 10/2/2014 17:30:02 T6


Please help me out guys

Thanks,

Chippi

Attach sample excel file

Hi,

Look at attached file.

Formula at F2 :

=iferror(B2-INDEX(B:B,SMALL(IF((C$2:C$13=IF(LEFT(C2,1)="I","T","I")&MID(C2,2,255))*(ABS(B$2:B$13-2)<--"0:00:10"),ROW(C$2:C$13)),1)),"")

end this array formula by hold and press Ctrl-Shift-Enter at the same time

Regards

You are welcome Chippi and thanks for the reputation point.
Please don't forget to mark the thread as solved.

=IFERROR(B2-INDEX(B:B,SMALL(IF((C$2:C$13=IF(LEFT(C2,1)="I","T","I")&MID(C2,2,255))*(ABS(B$2:B$13-B2)<--"0:00:10"),ROW(C$2:C$13)),1)),"")

Start from here :
IF(LEFT(C2,1)="I","T","I") --> this will find the pair alphabet, so if "I" then yield "T", and if "T" then yield "I"

then :
IF(LEFT(C2,1)="I","T","I")&MID(C2,2,255) --> this will add remaining number or the pairs, I2 will be T2, T3 will be I3 etc

Now use array formula to find matched candidates :
For example, lets take row 3, the value of cell C3 is I1, so the previous formula will yield T1

To find the matched candidates :
(C$2:C$13="T1")*(ABS(B$2:B$13-B3)<--"0:00:10")
this will return array with 12 items (12 = number of items in C2:C13) with value either True of False according whether it fullfills the two conditions.
First condition is check whether current cell = T1
Second condition is check whether difference time between current cell with any other cells in range B2:B13 is less than 10 seconds
Using multiply operation between first and second condition means both conditions must be True to yield True

The array operation is :

[?is? C2 = "T1" ] = False * [?is? ABS(B2 - B3) < 10 seconds] = True
[?is? C3 = "T1" ] = False * [?is? ABS(B3 - B3) < 10 seconds] = True
[?is? C4 = "T1" ] = False * [?is? ABS(B4 - B3) < 10 seconds] = True
[?is? C5 = "T1" ] = True * [?is? ABS(B5 - B3) < 10 seconds] = True
[?is? C6 = "T1" ] = False * [?is? ABS(B6 - B3) < 10 seconds] = False
[?is? C7 = "T1" ] = False * [?is? ABS(B7 - B3) < 10 seconds] = False
[?is? C8 = "T1" ] = False * [?is? ABS(B8 - B3) < 10 seconds] = False
[?is? C9 = "T1" ] = False * [?is? ABS(B9 - B3) < 10 seconds] = False
[?is? C10 = "T1" ] = False * [?is? ABS(B10 - B3) < 10 seconds] = False
[?is? C11 = "T1" ] = False * [?is? ABS(B11 - B3) < 10 seconds] = False
[?is? C12 = "T1" ] = False * [?is? ABS(B12 - B3) < 10 seconds] = False
[?is? C13 = "T1" ] = False * [?is? ABS(B13 - B3) < 10 seconds] = False

which result is :
False (= False * True)
False (= False * True)
False (= False * True)
True (= True * True)
False (= False * False)
False (= False * False)
False (= False * False)
False (= False * False)
False (= False * False)
False (= False * False)
False (= False * False)
False (= False * False)

Now using if() formula to change the member who has true value into row number where this array's member located ("False" members are not changed) :
IF(the_array_above, ROW(C$2:C$13))
will yield :
False
False
False
5 --> because the condition is true, the row number C5 which is 5 returned
False
False
False
False
False
False
False
False

Then we get the first item from the array using :
SMALL(above_array,1)
which will yield 5

And then we get the cell B5, using :
INDEX(B:B, SMALL(...))
INDEX(B:B, 5)

The different time between B3 and B5 is :
B3-INDEX(B:B, 5)
B3-B5

And to prevent error value showed up, we use :
IFERROR(B3-B5,"")


If you don't understand it yet, please don't be upset, array formula is hard to learn, you can googling and start learning from simpler examples and advancing to master it.

Regards