Formula to Compare and Replace Numbers Between two Sheets

**leo2308** · 09-10-2009, 12:27 PM

Ok 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 figure out a formula (if its possible) that will recognise the mismatched calls (rows) (can be done simply by comparing the call duration and dialled number), then if it is a mismatch, look for the correct entry and replace it with that one.

The tricky bit is that the matching call durations can sometimes differ by one or two seconds, which is fine, it is the same call, it is when it is more than 2 seconds that there is a mismatch...

It should end up with all the seperate rows in the first sheet (asterisk) having the call durations (rows F and G) and dialled numbers (rows K and L) from both sheets matching.

This is pretty complex and hell to explain properly, but any help would be massively appreciated!

**leo2308** · 09-11-2009, 04:39 AM

Ok, instead of finding mismatches, im thinking of just creating 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 you have had 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...

**rainlm** · 09-11-2009, 10:47 AM

So, you are trying to fill in the correct number of seconds into column G, correct? Since you have more than one condition (i.e. the ANI and the number of seconds give or take), a sumproduct formula with all your conditions might be a good bet. Try this in Cell G2:

=SUMPRODUCT(--(Squire!$H$1:$H$13=Asterisk!K2),--(Squire!$D$1:$D$13<(Asterisk!F2+2)),--(Squire!$D$1:$D$13>(Asterisk!F2-2)),(Squire!$D$1:$D$13))

**leo2308** · 09-14-2009, 10:28 AM

Wow, thank you very much, this works perfectly for the small test spreadsheet I posted, I came across a bit of difficulty when applying the formula to the original big spreadsheet, and posted an explanation here of the problem, but have since figured out a solution.

Thanks!

**leo2308** · 09-18-2009, 06:00 AM

Ok after running the formula's through the original proper spreadsheet, it appeared that all was good, but after closer inpection of the results, I found some errors.

Some of the rows in Squire row H do not have phone numbers, so when the formula is run for that row, row G in Asterisk fails to be populated with the correct matching call duration from Squire row D, as the formula uses the phone numbers as one of the conditions to match.

The only unique identifier left to use for matching the rows in squire to rows in asterisk is the time of call, (rows a in squire and asterisk), however they are roughly +/- 60 minutes apart, and can fluxuate by a few seconds to a few minutes difference, (for example 18:23:36 in squire matches to 05:24:17 in asterisk) and in both sheets they are in sequential order, the next row is always later (timewise) than the previous. Currently I am unable to find a formula which can match rows on this basis ..

The other problem I found is where there are many rows using the same exact phone number, in one case there are 67 rows using the same number. The call duration in seconds is very similar in many of them, and again the only unique identifier between these rows is the time of call. This causes the formula not to work correctly as there are too many rows with the same phone number and similar call duration to be correctly differentiated by the formula.

Again, if there is some way the formula can be amended to use the time of call as a condition to match the rows, any help would be very appreciated!

I have uploaded a sample spreadsheet outlining the problem.