Ok, I posted this in the 'New User' subforum, but am not having much luck there.
I am not that great with Excel, I know the simple stuff, but I have been asked to come up with a formula for a large spreadsheet which lists telephone calls and their call setup durations.
I have to explain this in depth because it is quite a complex spreadsheet
The calls go through two different telecoms equipment at the same time, and the spreadsheet lists the details of this (duration, time of call, number, etc, etc, etc).
There are two sheets, the first sheet gets info from the second one, and this is used to compare the call duration in seconds.
Most of the call records in the second sheet match up to the corresponding call in the first sheet, (when i say match up i mean they are within 2 seconds difference). However there are a few call records which do not match up. this can be verified by looking at the duration of the call, and the dialled number, together with the time of call.
I have attached up a small part of the spreadsheet with colour coded examples of where and how the calls do not match up.
I need to create a formula to correctly add the values in sheet 2 to to the rows in sheet 1.
I have come up with what I think is the most effective logic to create this formula, all I need is someone to help convert this logic into the cold hard formula
Im going to assume if you are still reading then you will have a look at the spreadsheet I attached, so I will explain it as if you are looking at it.
Formula logic:
Asterisk (sheet 1), G2 = cell value of Squire (sheet 2) column D, on row (x) where the value of Asterisk K2 = value in Squire column H
AND WHERE
Asterisk F2 is = or within +/- 2 of row (x) value in Squire column D
// this is done because there can sometimes be rows in squire column h with the same value, to narrow it down to the correct row we use this AND statement
Then once the correct value for Asterisk G2 is found, the values of Asterisk B2 and L2 can be taken from the same row where the result was found for Asterisk G2 on Squire Column A and H respectively.
Someone.... anyone... please...
thanks!
Bookmarks