Matching data in different sheets but slight difference in time va

[Twiggy]

Hi
Please help.
I have two sheets as follows:

Sheet 1:
Date / Time / Number Dailled / Duration
/Cost
22/1/10 10:00 01234123456 00:01:57 0.789

Sheet 2:
Date / Time / Number Dailled / Duration
/Extension
22/1/10 10:02 01234123456 00:01:56 1501

I am trying to put the cost of the phone call against the relevant extension
on sheet 2 that has dialled the number dialled. Obviously I can match the
number dialled in both cases, and the date, but there may be calls made to
that same number by that same extension on that same date but at a different
time, so I need to incorporate the time into the formula, to say if the date
matches and the number dialled matches, and the time difference between the
two is (say) less than 3 minutes (as that is about the difference I am
looking at) then give me the cost of that call in the column next to the
extension number on Sheet 2.

Sorry for the waffle but can anybody help.
Thank you

 

[Bernard Liengme]

If I understand the question, this might work for you
=SUMPRODUCT(--(Sheet1!A2:A10=A2),--(Sheet1!C2:C10=C2),--(Sheet1!B2:B10>=B2-TIME(0,3,0)),--(Sheet1!B2:B10<=B2+TIME(0,3,0)),Sheet1!E2:E10)

I put you data in row 1 for the two sheets (with headers) and made up some
extra data down to row 10. You will need to adjust the ranges. Unless you
have Excel 2007, SUMPRODUCT cannot use full column ranges as in A:A. The
third and fourth terms specify that the times in Sheet1 must be within
plus/minus 3 mins of the time in Sheet2

best wishes

 

[Twiggy]

Hi Bernard

Many thanks for your help but I am getting an #Value!?

Sheet 1
Call start date Call start time Number Duration Cost
28/01/2010 11:47:16 0800100607 00:01:58 £0.15
22/01/2010 13:15:57 0800420421 00:00:07 £0.00
22/01/2010 16:52:15 0800602090 00:00:44 £0.00
26/01/2010 16:12:08 0800800160 00:00:54 £0.00
15/01/2010 11:21:28 01132223222 00:00:52 £0.01

Sheet 2 (there is a sum total in row 2)
Start Time Telephone No Duration First Rang
28/01/2010 11:49:46 0800100607 117 1011 #VALUE!
22/01/2010 13:19:26 0800420421 6 1510
22/01/2010 16:55:40 0800602090 44 1545
26/01/2010 16:15:44 0800800160 53 1513
15/01/2010 11:24:41 01132221234 51 1032
29/01/2010 12:16:31 01132345678 41 1557

This is my formula
=SUMPRODUCT(--(Sheet1!A3:A4350=A2),--(Sheet1!C3:C4350=C2),--(Sheet1!B2:B4350>=B2-TIME(0,3,0)),--(Sheet1!B2:B4350<=B2+TIME(0,3,0)),Sheet1!E2:E4350)

where am i going wrong. There is a lot more data in Sheet2 so they dont
follow in the same order on both sheets.

 

[Twiggy]

Hi Bernard

I think I have done it, but I cannot get it to calculate!
The worksheet is about 2665kb in size but when I press the f9 to calculate
the "calculating2 processor(s)):0%" doesnt move - left it for 15 mins.
Is that my memory on my machine or something else?

 

[Twiggy]

I believe it is the sumproduct - is there another formula I can use?
Also see the following revised worksheets.....

I have changed the formula to be in Sheet1 so that the formula represents
the relevant extension in sheet1 that is in sheet2 using the following formula

=SUMPRODUCT(--(Sheet2!B:B=A2995),--(Sheet2!F:F=H2995),--(B2995+TIME(0,4,0)>=Sheet2!C:C),--(B2995-TIME(0,2,0)<=Sheet2!C:C),Sheet2!L:L)

Here is a sample of records from sheet 1 with the resulting extension at the
end. The third column is duration (in seconds). Why am I getting 3095 and
3098's - they should go no higher that 1599? Its adding two columns for some
reason as the calls are quite close together:


14/01/2010 15:53:24 40 07899941346 £0.035 1549
14/01/2010 15:55:48 609 07899941346 £0.533 3095
14/01/2010 17:04:37 52 07899941346 £0.046 1549
15/01/2010 08:27:49 229 07899941346 £0.200 1549
15/01/2010 08:34:16 11 07899941346 £0.010 1547
15/01/2010 08:38:25 5 07899941346 £0.004 1547
15/01/2010 08:45:37 6 07899941346 £0.005 3098
15/01/2010 08:46:14 30 07899941346 £0.026 3098

This is sheet 2 with the call data- duration here is column 4:

14/01/2010 15:55:52 07899941346 38 1549
14/01/2010 15:58:28 07899941346 608 1546
14/01/2010 17:06:59 07899941346 51 1549
15/01/2010 08:30:38 07899941346 228 1549
15/01/2010 08:37:15 07899941346 12 1547
15/01/2010 08:41:36 07899941346 4 1547
15/01/2010 08:48:37 07899941346 5 1549
15/01/2010 08:49:11 07899941346 29 1549

I have tried incorporating the duration Sheet1 - 1 second is not necessarily
sheet 2 - it could be Sheet1 plus 1 second to match Sheet 2.

Sorry about the waffle but hope making sense

 

[Bernard Liengme]

Not sure how we get to A2995 !
Want to send me a sample file? Get my email from my website
You are using Excel 2007, I hope since full column references like B:B are
not legal in earlier versions
best wishes