Matching call data based on date, time and number called to give c

  • Thread starter Thread starter Twiggy
  • Start date Start date
T

Twiggy

I have the following two tables on different sheets, both containing other
information within them that is irrelevant in my question.

SHEET 1
Date - Time - Number Called Duration Cost
01/02/10 09:05:21 01234123456 02:16 0.50

SHEET 2
Date - Time Number Called - Duration Extension
01/02/10 09:07:56 01234123456 02:15 1234
01/02/10 15:30:45 01234123456 01:59 1234

Sheet 2 HAS A LOT MORE RECORDS THAN SHEET 1 AND THERE ARE A NUMBER OF CALLS
TO THE SAME NUMBER EACH SAME DAY AT DIFFERENT TIMES DURING THE DAY.

What I am trying to do is add another column on sheet 2, to match that
specific call to the one in Sheet 1 and provide me the cost charged from
Sheet 1. My other problem is that although the date and number dialled are
equal the times and durations are slightly adrift (no more than 4 minutes).

Think I need an if and a vlookup but can anybody assist

Thank you
 
Interesting. A VLOOKUP based solution might work but I'm not sure I
altogether understand the problem:

If there are a lot more entries in Sheet2 than there are in Sheet1, can
multiple entries on Sheet 2 match a single entry in Sheet 1? If so, does the
spreadsheet need to count how many entries on Sheet 2 match the same entry on
Sheet 1 and divide up the cost among them?

Or do some of the entries on Sheet 2 not match any entry on Sheet 1? If so,
what’s the rule to determine that there’s no match? Can’t find any entry in
Sheet 1 within 4 minutes of the time on Sheet 2?

Can calls be closer together than the time skew so that the following
situation is possible?

SHEET 1
Date - Time - Number Called Duration Cost
01/02/10 09:05:21 01234123456 02:16 0.50
01/02/10 09:05:24 01234123456 02:16 0.50

SHEET 2
Date - Time Number Called - Duration Extension
01/02/10 09:05:23 01234123456 02:15 1234

If so, what would be the rule to decide whether the entry on sheet 2 matches
the first or second entry on sheet 1?
 
Back
Top