macro to find phone number and label with text

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.

0
timleft
9/3/2007 4:59:03 AM
access 16762 articles. 3 followers. Follow

1 Replies
754 Views

Similar Articles

[PageSpeed] 31

On Sun, 02 Sep 2007 21:59:03 -0700, timleft@gmail.com wrote:

>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]
0
John
9/3/2007 6:15:54 AM
Reply:

Similar Artilces:

Macro Function
How do I create a new function in Excel? -- Tomas Hi Thomas, You can have a look at this link: http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html Regards, Bondi THANKS!!!! Very good tip -- Tomas "Bondi" skrev: > Hi Thomas, > > You can have a look at this link: > > http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html > > Regards, > Bondi > > ...

Rich Text control formatted as bold??
I have a control field on my form that is setup as textformat = rich text. In the memo field on the form I need specific parts of the text to show up as bold. Upon form load I am populating the field with string data such as: Me.MyTextBox = "This is a test string generation." I need to set bold only one or two words of this string. The way I understood it was that if I was using Rich Text format it would convert the formatting to HTML style. But I don't see and havent found examples HTML formatting like [b] [/b] working in VBA. What is the correct way I can do t...

Summing sets of numbers but with special conditions
Hi - I need a formula to do the following. I have 2 columns of six numbers - for example: A B 1 75 70 2 83 69 3 80 72 4 84 67 5 95 83 6 93 80 The formula needs to do the following: 1. Find the lowest number in column A (75 in row 1 in the above example) 2. Discard the number in corresponding cell in column B (70 in cell B1) 3. From the remaining numbers in column B, discard the highest (83 in cell B5) 4. Add together the remaining 4 numbers in column B (69, 72, 67, 80) and add this result to the number found in step 1 (75) to give a total (363) Any ideas/pointers most w...

Sorting numbers with multiple decimal points?
Hi - I'm sure this is covered in some docs somewhere, but it's hard to know what to search for. I have a spreadsheet (using Excel 2003 SP1) in which I have a number of rows that have an "ID" field taking the format "X.X.X.X" where X is a number from 0 to 100. Like this: 1.1.0.0 blah blah rest of row 1.1.2.4 blah blah rest of row 1.1.3.0 blah blah rest of row 1.0.0.0 blah blah rest of row 1.1.3.2 blah blah rest of row 1.1.3.1 blah blah rest of row 1.1.1.1 blah blah rest of row 1.1.3.0 blah blah rest of row 1.1.1.3 blah blah rest of row 1.1.2.5 blah blah rest of ...

Auto-generate number
Hi, Has anyone tried creating a auto-incrementing number field in Contact? Should this field be stored in a separate database from the CRM database? Is this actually possible? To link a field in Contact to a external database. cheers. CW When would you want it to increment? "CW" <chuanwee72@yahoo.com> wrote in message news:1e29201c454ec$52ba90b0$a401280a@phx.gbl... > Hi, > > Has anyone tried creating a auto-incrementing number > field in Contact? > > Should this field be stored in a separate database from > the CRM database? > Is this actually p...

Label for imported Outlook holidays
Hello all, Exchange 2003 SP2/Outlook 2003. We modified the outlook.hol file so that it contains the days our company will be closed in 2009. The file has been copied on each user's workstation, and each user has to importe the holidays in his/ her Outlook. Question is : is it possible to add a label during the import ? Or to change the status/create a custom status ? In this way we could easily see in the weekly/monthly view which days are off, without needing to modify each item individually. Thanks ! Luuke you can't create a custom status. users would need to add a label to th...

Macro is not logging out of Great Plains
I created a macro to login, run checklinks and then log out. I set it up in the Task Scheduler on my system to run after hours, using a bat file. It worked for several months, and then in January it didn't finish the macro and log out. I would come in the morning, and I would still be logged in and the scheduled task was still running. I had to end task and delete my login in Great Plains. I have tried recreating the macro, I have tried running it on the server, and I am still having the same problem. In most cases, the report is generated - so I know the checklinks are finishing...

select case to replace text with different text
I'm trying to use a Select Case in a Private Sub Worksheet_Change event to do the following: if I type w in a cell in col B, I want to replace it with WIDGETS if I type g in a cell in col B, I want to replace it with GIDGETS seems like it should be simple but I can't come up with the code. On Sun, 10 Jan 2010 10:28:54 -0600, "John" <nodak457@yahoo.com> wrote: >I'm trying to use a Select Case in a Private Sub Worksheet_Change event to >do the following: > >if I type w in a cell in col B, I want to replace it with WIDGETS >if ...

Writing text to a CMDIFrameWnd
Hi All I'm trying to display some information about my main window in the control bar, specifically the magnification and the x & y coordinates of the mouse. After some work I got the Combo Box that displays the magnification working but I'm stumped on the coordinates. First thing is the VC++ class wizard doesn't seem to generate a DoDataExchange function for CMDIFrameWnds so I had to write it myself, but once I did, the ComboBox was happy. I've defined the mouse coordinates as static text, IDC_XVAL and ICD_YVAL, in the Dialog bar, though they don't show up as the ...

Getting date stored as text into real date?
A database query program outputs everything as a text string. One of the fields is a date, formatted as yyyymmdd. Is there a worksheet function that will change this to an Excel-recognized date? Or a macro? The error checking doesn't flag this. Ed With your text date in A1, try this in B1: =DATE(LEFT(A1,4),MID(A1,5,2),(RIGHT(A1,2)) Hope this helps. Pete On Dec 17, 1:12 pm, Ed from AZ <prof_ofw...@yahoo.com> wrote: > A database query program outputs everything as a text string. One of > the fields is a date, formatted as yyyymmdd. Is there a worksheet > function t...

Non-VBA formula to find 2nd Sunday of a given month
Can anyone help me write a formula to find the date of the second sunda in a given month? Thanks in Advance, Dav ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com David, Assuming you have a date in A1, this gives the 2nd Sunday of that date =DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+7 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "DavidObeid&q...

Macro
Is it possible to add a macro as a custom POS button? Thank you, Sylvain ...

Sorting rows out of order after all rows are numbered in sequence
Data is in 4 columns with Headers described below: Col A - Line# (rows are numbered in original order in worksheet) Col B - Acct# (property number) Col C - Category (code for the type of information in the rows ie: E, V, X, T) Col D - Label (description of the data or values in the row) There is one row of Expenses (all begin with "LOE - ") that is numbered out of sequence from the other "LOE -" items. I need to Move it up with the other "LOE -" items and if possible Re-sort all the "LOE -" items BUT only the "LOE -" rows within e...

Print or Print Preview not showing text
Print or Print Preview not showing text using PP2007 just released SP2 for office Now when we try to use print or print preview only see some of the text headers. None of the text is showing or the background or images, using built in templates. Worked bofore After testing, I found I have to set the color/greyscale - to colour to actually use the print preview. But it previews in grey scale, can't even see the preview in color. Unless I also go into printer settings and change that to color then get colour in preview. If I set it to grey scale and my printer is a ...

sum and times within text boxes
I want to multiply text boxs ie 4(1box)x $1.00(2box)=Total $4.00 (3box). I also want sum total (5 text boxes) for grand total. Is this feasible on form?? Thanks On Mon, 14 Jan 2008 18:58:01 -0800, He cries for help <Hecriesforhelp@discussions.microsoft.com> wrote: Have you tried an expression like: =[1box] * [2box] (assuming your control names are 1box and 2box. This expression would go in 3box' Control Source property. -Tom. >I want to multiply text boxs ie 4(1box)x $1.00(2box)=Total $4.00 (3box). >I also want sum total (5 text boxes) for grand total. > >Is this ...

Macro for checkbox (2) #2
Hi Still does not work new message is Sub or function not defined. I normaly just record macros so this is a bid more of a chalange Sub Macro1() ' ' If CheckBoxes(46).Value = True Then Range("O54").FormulaR1C1 = "50000" Range("T54").FormulaR1C1 = "4000" Range("Y54").FormulaR1C1 = "4000" Range("AD54").FormulaR1C1 = "4000" Range("AI54").FormulaR1C1 = "4000" Range("AN54").FormulaR1C1 = "4000" Range("AS54").FormulaR1C1 = "...

address book not finding contact when putting in letters
I guess I have general questions about the find function on the top of the toolbar, I have a contact in the company field called netflix, when I type net in the find box, it does not show up, but if I type netflix, it does show up??? why is that , I have noticed this on several contacts Same thing with me I have a name and email address in the Outlook Contacts When sending an email, sometimes as I type the name the email appears, sometimes it does not "bob" <bob@donotspam> wrote in message news:epF4Pp1rEHA.4004@TK2MSFTNGP10.phx.gbl... >I guess I have general questions...

How to Wrap Text
I have a 267 characters texts field. MS Excel 2007 will not show all the texts. It showed a bunch of ## symbols in the cell. I have tried several tutorials on how to wrap texts but it didn't work. I have tried these: http://www.dummies.com/how-to/content/how-to-wrap-excel-2007-data-to-see-all-the-text.html http://blog.taragana.com/index.php/archive/how-to-wrap-text-in-ms-word-2007-and-ms-excel-2007/ http://www.5min.com/Video/How-to-Wrap-Really-Long-Text-in-Excel-110694838 And it didn't work. The cell still shows the ## symbols. Any help is appreciated. Format the cel...

Help with formula containing text
Hi I need some help on the following. I have a column of text, linked to other worksheets, that is continuously changing. I need to be alert if the same piece of text appears in the column more than twice, e.g. Tom Tom Jane Mary Mary Tom "ALERT" Thanks Mike -- mlhynes ------------------------------------------------------------------------ mlhynes's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=12959 View this thread: http://www.excelforum.com/showthread.php?threadid=401787 Mike Visit Chip Pearson's site for much help on duplicates. Fin...

CHANGING LEGEND TEXT
I wish to change the text in the legend box from say- (Series 1) to (MPG) or anything that has a relevance to the graph. This was done easily in Works Spreadsheet and previous versions of Excel. Using 2007 Student & Home version. Hi, Select the chart and use the ribbon Chart Tools > Design > Data > Select Data On the dialog select the appropriate series and Edit. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "mareng" <mareng@discussions.microsoft.com> wrote in message news:CDF2E10C-04F5-411E-94C0-2504B48C1900@microsoft.com... &...

Make Smartlist Payables trx find unposted batches for reconcile
Smartlist currently does not view unposted payables batches. Many people want to use this object to review unposted batches and to assist in reconciling AP to the G/L or to dump AP transaction history for auditors. he Payables Transaction Smartlist object pulls the distribution accounts from the Vendor master and not the actual posted distribution. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, f...

where do I find a shortcut lists for international characters
Is there a quick printout I can access in order to use spanish characters in my typing without having to do lots of special set ups? At my school we use a software satelite service and accessing the control panels and such is a non-existing concept for me. I am using Office 2003 Publisher. Insert, symbol List here http://tlt.its.psu.edu/suggestions/international/bylanguage/spanish.html -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "The Spaish Teacher" <The Spaish Teacher@discussions.microsoft.com> wrote in messa...

Excel 2007 Page numbering
Is there a way in 2007 to consectively number all the sheets beginning with one (1) until ....? Thank you in advance for your help. Do you mean consecutive page numbers when printing more than one worksheet? Select the sheets to print using CTRL + click or Shift + click Then set up the header or footer with &[Page] The selected sheets will print consecutively page-numbered. Gord Dibben MS Excel MVP On Fri, 30 Jan 2009 14:09:01 -0800, Dottie <Dottie@discussions.microsoft.com> wrote: >Is there a way in 2007 to consectively number all the sheets beginning with >one (1)...

where can i find the meaning of all icons next to a received mail
"Rolls" <Rolls@discussions.microsoft.com> wrote in message news:9729F859-6E44-46D8-A571-78E9A5977144@microsoft.com... > Please use this BIG WHITE SPACE to write your question. Have a look here: http://www.outlook-tips.net/howto/icons_mail.htm -- Asking a question? Please tell us the version of the application you are asking about, your OS, Service Pack level and the FULL contents of any error message(s) ...

finding where a file is stored
2003 Excel used to tell you where the file was if you did a Save-As. Now it doesn't. How do I find where a file is currently stored? What do mean by 'now it doesn't...' Nothing has changed... Anyway use this in any cell =CELL("filename",A1) to see the complet path and file name "Catherine" wrote: > 2003 Excel used to tell you where the file was if you did a Save-As. Now it > doesn't. How do I find where a file is currently stored? Thank you for your time. Perhaps I was mistaken about the version. I used to be able to go to Save-As, an...