A challenge for you? Querying one sheet against another

[Ian Usher]

First thing I should point out is:

I don't have access to Access therefore Excel is my only route towards what
I'm looking for, which is...

I need to compare my mobile phone bill (downloaded into a worksheet from my
online account pages) with the contents of my 'Contacts' exported from
Outlook. The contacts will be exported so that only my 'work' phone numbers
are in the worksheet, I then need to be able to compare this with my bill's
sheet and pull out (query > report) any phone calls with numbers that
match - the total time of these 'matched' calls will be the amount I claim
on expenses...

Can I do this with Excel (2000/2003 is what I've got) and if so, how?

Many TIA

Ian.

 

[macropod]

Hi Ian,

Suppose your called numbers are in column A, the call costs are in column B,
and the 'work' phone numbers are in column C on rows 1-10.

In cell D1, you could enter:
=SUMIF(A:A,C1,B:B)
and copy this down to row 10. This will give you the sum of the call costs
for each of the numbers in column C that corrrespond with the numbers in
column A.

Cheers

 

[Dick Kusleika]

Ian

How about another approach? You can automate Outlook instead of exporting
your contacts. Assume you have three columns of data in your exported phone
bill: A = Time, B = Phone number, C = Duration of Call. Now you can set a
reference (Tools - References) to the Microsoft Outlook Object Library and
use code like this

Sub MatchPhoneBill()

Dim olApp As Outlook.Application
Dim olCont As Object
Dim olNs As Outlook.NameSpace
Dim olFldr As Outlook.MAPIFolder

Dim rCell As Range
Dim rRng As Range
Dim sPhone As String

'All the entries in column A
With ActiveWorkbook.Sheets(1)
Set rRng = .Range("a2", .Range("A" & .Rows.Count).End(xlUp))
End With

'Create Outlook link
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set olFldr = olNs.GetDefaultFolder(olFolderContacts)

'Loop through all the phone records
For Each rCell In rRng.Cells

'Format the phone number to look like Outlooks
sPhone = "(" & Left(rCell.Offset(0, 1).Value, 3) & ") " _
& Mid(rCell.Offset(0, 1).Value, 4, 3) & "-" _
& Right(rCell.Offset(0, 1).Value, 4)

'Find the first contact with that phone number
Set olCont = olFldr.Items.Find("[BusinessTelephoneNumber] = " _
& Chr(34) & sPhone & Chr(34))

'If one is found, write the name to column D
If Not olCont Is Nothing Then
rCell.Offset(0, 3).Value = olCont.FullName
End If
Next rCell

Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing

End Sub

This will put the names of your contacts next to their record. Finally, run
a pivot table on the phone records with FullName as the row and Sum of
Duration as the Data. Anything under (Blank) in your pivot table means they
aren't in your Contacts. You'll have to adjust a few things like formatting
the phone number (it may not be necessary) and identifying the next blank
column to write the name.