macro to find phone number and label with text

[timleft]

Hi--

Here's the situation: I have long lists of satellite phone bills that
I need to track by individual phone users and I want to build a macro
that seeks out a specific number (example, macro searches "882 156 445
4554" and in the column next to it, automatically spits out "Dan's
phone."

In one Excel file, I have a growing list of satellite phone numbers
(~400 numbers) that I'm constantly adding to that I'm hoping the macro
can draw from. In the other set of files, I have individual phone
bills for each phone (about 18 separate excel files per month) without
any indication of who they're calling. Essentially, it's just a long
list of numbers they've dialed. By identifying each number they're
calling, I can then do a filter function for "work-related" vs. "non-
work related" calls.

Basically, the more money we save on these operational costs
(approximately $10,000/month in the rainy season), the more money goes
back to programs and projects here.

Is it possible to build a macro to do these sets of functions? Any
specific advice or links would be appreciated on how to go about doing
this.


I'm new here and am working for a humanitarian organization in Sudan.
There aren't many resources out here so I'm hoping this posting will
yield some solutions.

Thanks for your help.

 

[John W. Vinson]

Hi--

Here's the situation: I have long lists of satellite phone bills that
I need to track by individual phone users and I want to build a macro
that seeks out a specific number (example, macro searches "882 156 445
4554" and in the column next to it, automatically spits out "Dan's
phone."

A Macro is not the appropriate tool in Access (you *are* using Access...!?): a
Query is.

Create a Query with two fields - Phone and PhoneUser. Fill it with the number
and the name. Create a Query joining the two tables, joining on Phone. It will
give you exactly what you describe.

In one Excel file, I have a growing list of satellite phone numbers
(~400 numbers) that I'm constantly adding to that I'm hoping the macro
can draw from. In the other set of files, I have individual phone
bills for each phone (about 18 separate excel files per month) without
any indication of who they're calling. Essentially, it's just a long
list of numbers they've dialed. By identifying each number they're
calling, I can then do a filter function for "work-related" vs. "non-
work related" calls.

You can use Access to link to the Excel spreadsheets (File... Get External
Data... Link, choose Excel as "files of type".

If you don't have Access, please repost in an Excel newsgroup - I expect you
could use VLookUp() to do a similar job in Excel. Again, no macro should be
necessary.

John W. Vinson [MVP]