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

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


0
Utf
2/19/2010 7:58:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1110 Views

Similar Articles

[PageSpeed] 12

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?
0
Utf
2/22/2010 9:26:18 PM
Reply:

Similar Artilces:

How do I preserve text color when combining cell data
I'm using "&" to combine data from multiple cells. Each cell has text of a different color and I want to preserve those colors in the new combined cell. The new combined cell is formatting all the text to one color. Anyone have any suggestions on how to maintain the separate colors in the new cell? A formula can only return a value to a cell, so you cannot return formatting and such. =A1&A2 will return the value of A1 and A2 to the cell that has that formula entered in it. HTH Regards, Howard "CraigS" <CraigS@discussions.microsof...

Matching function or no match
I am using this formula to compare 2 list to see which numbers are not included in both column A and Col C. The formula I am using below pulls out the ones that i do have a match . Is there another function that I could use that would only pull out the ones that there is not a match? =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1) Thanks Uh =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),A1,"") -- HTH Bob "Donna" <donna@yahoo.com> wrote in message news:BE688A2B-3D42-45A0-AA3F-B65034C685E1@microsoft.com... > > I am using this for...

Extracting Data from Excel
Hi, Is it possible to use excel like a CRM software? Currently i am using excel to store my customer list, requests and enqueries. I would like to extract data from those information such as by company, location, and status. What would be the best way to implement this? Is there any guide to do this? Thanks in advance Regards, MY How about use of Autofilter & Advanced Filter? MVP Debra Dalgleish has some nice illustrations at: http://www.contextures.com/tiptech.html Check out the relevant links on her home page -- hth Max ----------------------------------------- Please reply in n...

Client E-Mail and Web E-Mail not matching
I've got a few complaints from about 4 users where e-mail they see in their Outlook Client is not showing up in their Outlook Web Client when they check their e-mail from a remote location. We're using Exchange 2003 with SP2 installed and all the users have Office Outlook 2003 with SP1 Installed also. This seems to only have been a problem since we upgraded to Exchange SP2 two weeks ago. (Although I can't confirm it, but I wasn't told of any problems before th upgrade.) We had to upgrade to SP2 because of the 16 Gig limit so rollback is not an option. Best as I can t...

deduplicate data in excel
How do I deduplicate data in Excel. I have a large database of names and addresses which I want to check to duplication You may want to read some of Chip Pearson's techniques for dealing with duplicates: http://www.cpearson.com/excel/duplicat.htm Julie Melbourne wrote: > > How do I deduplicate data in Excel. I have a large database of names and > addresses which I want to check to duplication -- Dave Peterson Thanks Dave I have looked at this site, but cannot get the formula to work? Must be doing something wrong. Have you tried it? Do you have a working spreadsheet wtih...

Matching cells #3
Here's my spreadsheet: A B C 1 1 1 2 3 3 3 4 6 4 5 10 5 8 11 6 9 7 10 8 9 10 11 12 13 How do I get the matching numbers on the same rows, like: A B C 1 1 1 2 3 3 3 4 4 5 5 6 6 7 8 8 9 9 10 10 10 11 11 12 13 13 I would just re-create the columns. 1. Copy column A into D. 2. In E1 put: =IF(COUNTIF(B:B,$A1),$A1,"") 3. Copy it over to F1 and then down as far as needed. 4. Select columns D thru F, copy them, and go to Edit > Paste Special > Value. Press OK. 5. Delete column A thru C. HTH Jason Atlanta, GA &g...

Autonumber numbers
Is there a way, in a new Access 2007 database, to start the numbers of an Autonumber field at a number other than 1? It would be very convenient for us to start at 10001. Marsh An autonumber field is not supposed to be used as a user's numbering system. If you want to have a numbering system, then you should create it by creating a number field and applying your math and logic to it. This way you can start it at whatever number you wish and can control number issuing... But as far as an autonumber is concerned... you are relying on something in a way you should not b...

receiveing the same email three times
My outlook express died on me, therefore I had to start using outlook 2000, which was fine bit of a pain to get used to but, anyway since using outlook 2000, I am getting the same email from everyone three times. Why is this? It is soing my head in as my outlook is very full and it is taking me some time to get rid of the ones that are duplicated. PLEASE HELP ANYONE!!!!!!!!!!!!!!!!!!!!! ...

Move data to right with variable rows
I have a large spreadsheet with corrupted data. I can identify several types of bad data and have correct the data in these rows but one particluar type is giving me problems. Because the spreadsheet is large I used filtering and visible cell commands to correct blocks of data. This seems to work fine but one type is still not working so I am trying a row by row logic search. In this case the bad data needs to move from cell column B to C with the range through L. So any data in the range B through L in the selected row is copied if the contents of L ="N". A cel...

Lookup Formula: Return 1st match, then 2nd match, then 3rd match
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B = Task. A B Bob Reconcile Cash Tim Do Sales Report Bob Create presentation Tim Prepare financial statements Bob Hire staff person Now on tab two, I want to create another list that pulls all the tasks together by person. For example, Tab 2 would look like this: Bob Reconcile Cash Bob Create presentation Bob Hire staff person Tim Do Sales Report Tim Prepare financial statement. I realize I can just sor...

Index Match Functions
Has anyone ever combined the Index and Match functions to do lookups? ...

Extract X data from Chart
I have looked through the information provided by this group for chart events, and I have a good code to get the embedded chart to show the message box with the point information. (Thanks Jon Peltier) What I would like to do now is to extract that X data and paste it into another area of the workbook to run some other analysis. Is there some way to have the X value be copied into another cell on another sheet? I am using this code in a MouseUp event: Dim ElementID As Long, Arg1 As Long, Arg2 As Long Dim myX As Variant, myY As Double With ActiveChart ' Pass x & ...

Not matching transactions
I have it set to not automatically do anything. I've tried resetting the account quite a few times with the help of technicians, but it's too long and involved, and I'm tired of rebalancing the account every time. I have 3 accounts, 1 works without passport and does fine. One of the others I can manually download from the web site, and have no problems doing it like so and decided this is the best way, prior tries it wouldn't work with passport. But the other still insists they are new transactions, and I can't download from their site directly. They only allow...

How do I import large data files?
When I try to import a data file with more rows than Excel, the text box says I can import the rest by re-importing the file and excluding the lines already imported. The Import Wizard doesn't seem to give me this option... suggestions? Hi Russell Maybe http://support.microsoft.com/default.aspx?scid=kb;EN-US;q120596 XL: Importing Text Files Larger Than ???? Rows -- Regards Ron de Bruin http://www.rondebruin.nl "Russell Seguin" <Russell Seguin@discussions.microsoft.com> wrote in message news:184036AB-8922-4EAA-85B6-05FAB3E577F5@microsoft.com... > When I try to...

data in row didnt match import type 12-30-05
while importing almost 800 accounts , i got the following error for majority of accounts 'Data in row did not match the import type' what is the solution? How can i match it? whats is the way of comparision? It looks like a data problem. You need to check if all dates are correct (and filled), if numbers are numbers (not letters), etc... Regards, -- Erik van Hoof CWR Mobility Check our weblog at: http://www.cwrmobility.com/weblog "Aam" <Aam@discussions.microsoft.com> wrote in message news:C51E8ED0-443D-4B35-B436-0A9EDB09B419@microsoft.com... > while impo...

Transfer In and Supplier Reorder Number
When I create a new inventory transfer in, and I add items to it, the Reorder Number column on the Content tab is never populated. I have verified that I've put a reorder number in all the places that I can find that allow me to enter one. But none of them appear in this column. The column is not populated on an export either. Is this a bug or am I missing something? I believe in order for the reorder number to populate you would have to select a supplier. Ex: if you're doing a purchase order and do not select the supplier - the reorder number will not populate. If you select t...

Manually matching transactions
Hi, I'm new to Money 2007 so maybe this is an easy question but I don't find an answer after searching help and recent posts. I have setup a checking account and a bill for Tuition. My transactions are automatically downloaded to my checking account. One of these transactions was my Tuition payment, but the bill still shows as being past due. Can't I simply mark that transaction as being a Tuition payment somehow? I'm sure I could record payment from the bill with the same info as that transaction has then delete the downloaded transaction that wasn't associated ...

My inbox isn't up to date
Hi there, The last email I received in my inbox is from Dec 15, 2009. How do I get it current? I have initiate the send/receive command several times and restarted outlook, but it doesn't update my recent emails. Thanks... And is there any err msg in this unknown version of outlook? "Sassa Oz" <Sassa Oz@discussions.microsoft.com> wrote in message news:D7B044C7-C915-4CE7-8A92-EF09EBE540E0@microsoft.com... > Hi there, > The last email I received in my inbox is from Dec 15, 2009. How do I get > it > current? I have initiate the send/receive co...

How does MATCH handle a Non-Match?
I would like a steer on how the MATCH Worksheetfunction behaves if it doesn't find an exact match of values in the array it is searching. I am looking through a series of Dates and instead of picking the nearest one - which I need and seems most obvious - it seems to pick the previous one. I.E. - Searching for 14 Oct 2005 In a list which contains (among others) 15 Sep 2005 16 Oct 2005 And it picks the 15 Sep row! PS. Cell values are actually 14/10/2005 but formatted as above. In Excel's Help for the MATCH function, it describes how the values are returned. Chris wrote: > I w...

Break out numerical data range
Hello~ I need some assistance with solving this issue, please. I have been provided with an Excel worksheet containing 2 columns and 250 rows of data. Each row contains a numerical range. Column A contains the first number of a range. Column B contains the last number of the same range (please see example below). Column A Column B 11234 11267 28210 28215 36748 36750 85726 86200 My task is to somehow "extract" all of the numbers within these ranges, and place ALL numbers into a single column. This is the only way that I will be able ...

Convert data from rows to columns
I have a range of information on a spreadsheet as follows: A B 1 Name: Tom Smith Job Title: Sales Organisation: Made up Telephone: 1111 111 111 Email: tom.smith@madeup.co.uk Subject: Not much I have another 300+ entries of data (of 8 rows exactly as above) totalling 2629 rows. the example above is how it appears on my spreadsheet i.e. headings and names in the same cell (Name: Tom Smith) and sometimes seperate cells (Email: / tom.smith@madeup.co.uk) I need to create 6 columns for Name/Job title/Organisation/Telephone/Email/Subject and then move the data into the relevant columns....

Index, Match, Min and Max question
I'm trying to retrieve a date (in column A) that corresponds with a Min and Max amount (in columns B:D). Basically I need to know when my amounts hit their Highs and Lows. Can someone help me with this please? Thanks, Maria Dates in column A; values in B Then =INDEX(A1:A9,MATCH(MAX(B1:B9),B1:B9,0)) Returns the date corresponding the max value in B Is this what was needed? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email <mgriffiths@klmtel.net> wrote in message news:1194464476.732636.234500@50g2000hsm.googlegroups.com... >...

Using IO completion port for BIDIRECTIONAL data exchange ?
Hello, a while back I asked some question about using IO completion port to exchange data between threads, and people here kindly helped me a lot especially Mr.NewCommer that has a valuable essay about this http://www.flounder.com/iocompletion.htm among other knowledge base essays. Now that I'm actually getting my feet wet with this and starting writing codes I find some more questions that I didn't figure it out earlier. Basically my problem i that I want to send and receive data to a device that is attached to a USB port, data rate can be as high as 100KB/Sec. So I created two ...

fuzzy logic matching in Excel
Is there such a thing as fuzzy logic matching in excel? For example, if I have Name Addr1 Addr2 Addr3 Davie Kings Road London England and Davie Kings Road - London This should match as a potential pair. How can I do this in Excel? Any ideas? Nothing built in. Davie wrote: > > Is there such a thing as fuzzy logic matching in excel? > > For example, if I have > Name Addr1 Addr2 Addr3 > Davie Kings Road London Eng...

Calling IEGetWriteableFolderPath()
Hi, I am trying to call IEGetWriteableFolderPath() from a C# BHO. So I have done this: [DllImport("ieframe.dll")] public static extern int IEGetWriteableFolderPath(ref Guid clsidFolderID, StringBuilder str); Later in a function: Guid FOLDERID_InternetCache1 = new Guid("{352481E8-33BE-4251-BA85-6007CAEDCF9D}"); StringBuilder Path = new StringBuilder(); int RES = IEGetWriteableFolderPath(ref FOLDERID_InternetCache1, Path); But I am not getting anything in Path! Also the return value (RES) is -2147467623 , don't know what this value means. Th...