Matching data in different sheets but slight difference in time va
 

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

Matching data in different sheets but slight difference in time va
 

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Twiggy" wrote in message
...
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

Matching data in different sheets but slight difference in tim
 

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.

"Bernard Liengme" wrote:

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Twiggy" wrote in message
...
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

.

Matching data in different sheets but slight difference in tim
 

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 "calculating:(2 processor(s)):0%" doesnt move - left it for 15 mins.
Is that my memory on my machine or something else?

"Twiggy" wrote:

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.

"Bernard Liengme" wrote:

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Twiggy" wrote in message
...
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

.

Matching data in different sheets but slight difference in tim
 

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



"Twiggy" wrote:

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 "calculating:(2 processor(s)):0%" doesnt move - left it for 15 mins.
Is that my memory on my machine or something else?

"Twiggy" wrote:

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.

"Bernard Liengme" wrote:

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Twiggy" wrote in message
...
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

.

Matching data in different sheets but slight difference in tim
 

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Twiggy" wrote in message
...
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



"Twiggy" wrote:

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 "calculating:(2 processor(s)):0%" doesnt move - left it for 15 mins.
Is that my memory on my machine or something else?

"Twiggy" wrote:

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.

"Bernard Liengme" wrote:

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Twiggy" wrote in message
...
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

.