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

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
 
S

SVanDee

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top