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 :

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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