Two excel problems

I am using Excel 2000, I have two problem, one small, one big

Small problem
I have a worksheet with just under 5000 rows and half a dozen columns. Two
of the columns contain telephone numbers. Unfortunately in the conversion
of the data to get it into a format that excel could use the leading "0"
was stripped from the phone number, how can I put the "0" back in again (I
don't have access to the original data so I can't rerun the conversion).

Big problem
This ones a little harder, I have a worksheet with around 40 telephone
number on it in one column (call this worksheet Y). I have another 10
worksheets (call these worksheet a,b,c,d etc.) which contain imported
telephone bill information (numbers called, duration, cost etc.). What I
want to do is see how often the number listed on worksheet Y appears on
each of the worksheets a, b, c, etc. Anyone tell me how??

Regards

Tim
0
bog (6)
9/13/2006 9:14:38 PM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
316 Views

Similar Articles

[PageSpeed] 43

"Tim" wrote:
> .. Small problem
> I have a worksheet with just under 5000 rows and half a dozen columns. Two
> of the columns contain telephone numbers. Unfortunately in the conversion
> of the data to get it into a format that excel could use the leading "0"
> was stripped from the phone number, how can I put the "0" back in again (I
> don't have access to the original data so I can't rerun the conversion).

Something which could fix the small problem ..

Assume tel#s' running in A1 down, and should be in 7 digits format
Put in B1: =IF(A1="","",TEXT(A1,"0000000"))   
(Adapt the number of zeros to suit)
Copy B1 down. Col B will return the tel#s with the leading zeros (if 
applicable). Then select col B > copy, and overwrite col A with a paste 
special as values. Delete col B to clean up. Repeat for the other col of 
tel#s.  
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
0
demechanik (4694)
9/14/2006 1:38:02 AM
"Tim" wrote:
> .. Big problem
> This ones a little harder, I have a worksheet with around 40 telephone
> number on it in one column (call this worksheet Y). I have another 10
> worksheets (call these worksheet a,b,c,d etc.) which contain imported
> telephone bill information (numbers called, duration, cost etc.). What I
> want to do is see how often the number listed on worksheet Y appears on
> each of the worksheets a, b, c, etc.

In Y, 

Assume the 40 tel nos are listed in A2 down
Assume the tel nos on the 10 sheets (a, b, c, d, etc) are all in col A
(The tel nos in Y -- with the "small problem" rectified -- and those in the 
10 sheets are assumed to be in a consistent format)
 
List the names of the 10 sheets in B1 across, eg: a, b, c, d, etc 
Ensure that these names are consistent with the actual names on the 10 tabs. 
Watch out for typos, extra spaces, etc 

Then just put in B2:
=IF(OR($A2="",B$1=""),"",COUNTIF(INDIRECT("'"&B$1&"'!A:A"),$A2))
Copy B2 across and fill down to populate the table
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
0
demechanik (4694)
9/14/2006 1:52:01 AM
I've posted 2 responses for you to try, one for the small prob, another for 
the big prob. See:
http://makeashorterlink.com/?K251413CD
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"Tim" <bog@off.com> wrote in message 
news:2x_Ng.28$s4.14@newsfe3-win.ntli.net...
>
> I am using Excel 2000, I have two problem, one small, one big
>
> Small problem
> I have a worksheet with just under 5000 rows and half a dozen columns. Two
> of the columns contain telephone numbers. Unfortunately in the conversion
> of the data to get it into a format that excel could use the leading "0"
> was stripped from the phone number, how can I put the "0" back in again (I
> don't have access to the original data so I can't rerun the conversion).
>
> Big problem
> This ones a little harder, I have a worksheet with around 40 telephone
> number on it in one column (call this worksheet Y). I have another 10
> worksheets (call these worksheet a,b,c,d etc.) which contain imported
> telephone bill information (numbers called, duration, cost etc.). What I
> want to do is see how often the number listed on worksheet Y appears on
> each of the worksheets a, b, c, etc. Anyone tell me how??
>
> Regards
>
> Tim 


0
demechanik (4694)
9/14/2006 7:20:23 AM
Max wrote:

> "Tim" wrote:
>> .. Small problem
>> I have a worksheet with just under 5000 rows and half a dozen columns.
>> Two of the columns contain telephone numbers. Unfortunately in the
>> conversion of the data to get it into a format that excel could use the
>> leading "0" was stripped from the phone number, how can I put the "0"
>> back in again (I don't have access to the original data so I can't rerun
>> the conversion).
> 
> Something which could fix the small problem ..
> 
> Assume tel#s' running in A1 down, and should be in 7 digits format
> Put in B1: =IF(A1="","",TEXT(A1,"0000000"))
> (Adapt the number of zeros to suit)
> Copy B1 down. Col B will return the tel#s with the leading zeros (if
> applicable). Then select col B > copy, and overwrite col A with a paste
> special as values. Delete col B to clean up. Repeat for the other col of
> tel#s.


Worked perfectly thanks Maxs

Tim
0
bog (6)
9/14/2006 3:56:50 PM
"Tim" wrote:
> Worked perfectly thanks Maxs

You're welcome, Tim. 
How did the other suggestion go for the "big problem" ?
Let me know ..
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
0
demechanik (4694)
9/15/2006 1:09:02 AM
Reply:

Similar Artilces:

Directory Problem When Opening Files
Had this problem with my PC at work last week and the solution chosen by my company's Help Desk people was to uninstall and then reinstall the whole Microsoft Office package of programs. And while that fixed my problem, I've got to believe that there is just one single file that needs to be deleted or somehow reinitialized. The problem was fixed last week with the uninstall and reinstall, but popped up again this afternoon. Hope one of you can help me with this. Here's the problem ... When I try to open an Excel spreadsheet or attach a file to an Outlook message, the wi...

crosstab query problem
I use MSO2K. When i create a new crosstab query using the wizard, none of the fields are getting listed in the tables. Similar problem I find with Unmatched query wizard also. I have used both these wizards in the past on the same system. Unable to recall anything which has been done on the system which could have caused this problem. I have to do a Detect and Repair operation. Problem still persists. Any inputs on what could the problem be? Thanks Ramesh Ive seen this happen before as well, certain fields just refuse to show up in the wizards (seemingly always the one...

Exporting to Excel #4
Can I download the translator to export contacts to Excel? I don't have the install disk. ...

Excel #32
Have new laptop and have activated 2007 Office & Student. I put several spreadsheets from my desktop on a disk and used the new system to update the file. This worked fine, but when I tried to save the file a "save as" box appeared that was saving a copy of my document. When I used save button a notice appeared that said "Path does ot exist. Check the path and try again" What do I need to do? Where are you trying to save the documents to? Excel will automatically try to save back to the original path, this will be your backup disk if you haven't copied...

synchiing two computers?
Is there a way to synch Outlook on two computers? I end up with some email on my laptop and some on my desktop - it would be nice if the two could be synched so that I could have all of my email on both computers. Take a look here, it may help: http://www.slipstick.com/outlook/sync.htm -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Bill Andersen asked: | Is there a way to synch Outlook on two computers? | I end up with some...

Pivot table in Excel 2007
Hello experts This pivot table has been created automatically in Excel 2007 : see the following link http://dl-1.free.fr/52616e646f6d495687b5f6522854b42d0ab1c5c335e8e7d2b3d67f4e6bf6721d/Pivottable2007template.xlsx In this pivot table, how, you experts, will you replace the row "Grand total" by a row "Difference" with a formula like Receipts - Expenses. Thanks for your help! Hi One way would be to have another column in the source data, called Expense2 with a formula =Expenses*-1 (replacing Expenses with the relevant cell reference) and copying down. Replace Expens...

Excel 2007: Disable Office Menu control on the fly
I'm looking for a way to disable Office Menu controls such as: New, Save, SaveAs, etc on the fly. Whenever user opens an excel workbook into my application, those controls under Office Menu are disable. In office 2003, I could do something like this when user opens their workbook in my application: With objExcel.CommandBars("Worksheet Menu Bar") .Controls("&File").Controls("&New...").Enabled = False .Controls("&File").Controls("&Open...").Enabled = False .Controls("&File").Controls(&qu...

Hid a sheet in Excel 2003 Need to unhide it in Excel 2007
I hid a sheet on a workbook created with Excel 2003. Our company upgraded to Office 2007 and I tried to unhide the sheet in Excel 2007. 2007 doesn't seem to find the previous sheet. Right click on any tab and select Unhide. Does the Unhide pop up window appear or not? "Lid7sgi" wrote: > I hid a sheet on a workbook created with Excel 2003. Our company upgraded to > Office 2007 and I tried to unhide the sheet in Excel 2007. 2007 doesn't seem > to find the previous sheet. ...

Real problems with DCount
I need to count the number of values in a column that have been recorded with the Yes/No tick box on the main form of my database So far, I have tried =DCount("*","[Table1]","[sleep risk]='true'") =DCount("*","[Table1]","[sleep risk]='yes'") and a few others that don't work either. Secondly, I also have to count a number of dates entered in a column [date fsmp sent] to give me the total number of date entries in that column that were witihn the last 90 days from Date(). I don't know if Date() or Now() is ...

Changing file permissions when saving Word or Excel documents
Hi, I work in an office environment which consists primarily of PCs using Office (Word, Excel, etc.). I have found that when I save an existing Word or Excel file (Office 2004 with all the updates) which is on a network drive with multiple user and group permissions, the write permission reverts to just me, instead of the multiple users and groups, i.e., it becomes read-only for everyone else. It doesn't matter if the file is open with "(shared)" access on Windows first, if I save it, it becomes read-only to everyone else. Currently, I have to remember to immediately drop int...

Compare two cells in two different files and return answer
I am attempting to compare two text cells and if they both exist, I want to bring in a "yes" answer. If they don't, bring in a "no" answer. Here's an example of text. File 1 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH ZPB_COMMON File 2 Col A Col B BRATTA ZPB_COMMON AHERNC ZPB_COMMON GENCABH So, what I want to do is say in File 1 take cells A1 and B1 compare them to File 2 and search Col A and Col B and if there is an exact mat...

Unable to view some Excel files on Network
I am running Office 97 Pro on a Windows 98 Workstation on a Novel network. We have applied all the patches that we know of to bot Office and Windows 98. Upon roll out the user notified me that sh cant get into a certain file for her to do her jobI have uninstalle Office and rebuilt this machine and still get an illegal operation error. The error says "EXCEL caused an invalid page fault in modul EXCEL.EXE at 016f:30002924". No-one including network administrator can access certain Excel documents that are stored on the network fro a certain computer but other documents are fine. I...

Are there limitations to OLAP in Excel?
When creating a pivot table based on a Cube in Analysis Services, everything appears to work until I add a fourth dimension to the pivot table. Once I do that, I get a message that says "Running OLAP Query" for a bit, and then all of my fields come back blank. Any ideas? There are no error messages prodcued, so I am a little stumped. ...

Pivot table for two different data variables
I have the following table Name type Cost overhead amy C1 23 12 bob C2 129 17 cat C2 36 45 doo C2 100 32 I owuld like to create a pivot table that looks similar to: the standard pivot table. (Please ignore type for now.) name Data Total amy Sum of overhead 200 Sum of cost 12 bob Sum of overhead 130 Sum of cost 23 cat Sum of overhead 90 Sum of cost 19 doo Sum of overhead 87 Sum of cost 34 Total Sum of overhead 507 Total Sum of cost 88 But with overall total (overhead + cost) for ...

Contracts Advanced Find Excel Export
When I perform an Advanced Find on "Contracts", the Export to Excel icon is not available when the search results are displayed. Is there a way to export Contract Advanced Find Results? Thanks Not sure why Microsoft didn't put the export to Excel function in the Contract Advanced Find view, but if you click File > Print, you will get a print window where you can select all and copy/paste into Excel and get pretty much the same results. -- Matt Wittemann http://icu-mscrm.blogspot.com "Rocco P" wrote: > When I perform an Advanced Find on "Contracts&q...

How do I annotate the cell results from Excel to a Hyperlink?
I have a cell that has a part number in it that I have generated and I want to go to the web to find pricing on it. I have a link to a web page for pricing and all I need to do it to annotate this part number from the cell to the end of the hyperlink that I have. How do I do this? Thanks Let's say you want to add "abc" to the end of the following text "http://www.abccorp.com/" and "abc" is in cell A1 =HYPERLINK("http://www.abccorp.com/"&A1) "kgagne" wrote: > I have a cell that has a part number in it that I have generated...

problem about installation
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I bought a office:mac 2008 home&student edition, which can not be successfully installed. I have already type in the product key and verified my username and password, then it showed that no installation document can be found. what should I do about this? "mat@officeformac.com" wrote: > I bought a office:mac 2008 home&student edition, which can not be successfully > installed. I have already type in the product key and verified my username and > password, then it showed that no installation document can be fo...

COUNTIF (?) on two criteria
I have a spreadsheet that logs work activities and records team members' completion of project units. Column B records team member names. Column C has project unit status - 'pending', 'beta' or 'completed'. It's easy to set up a formula to count how many units are allocated to each team member - =COUNTIF(B1:B1000,"Joe"). But what I need to do is set up formulas to count how many units Joe has completed, or has pending or at beta, and this means assessing data in both columns - which COUNTIF won't do. This doesn't strike me as an especially...

How from Word a list separated by commas convert to Excel?
In Word 2002, I have a list of email adds separated by commas followed by forenames & surnames, how may I convert this list in tabulated form to Excel... A column for the email add, a column for the forename and a column for the surname... e.g ferreira @benco.co.uk,, Ferreira, Marnie italia@web.co.uk,, inglese, ANDREA info@inter.co.uk,, Mulondo, Mashudu neu@mond.co.uk,, Urbina, Ivonne brenna@web.co.uk,, Williams, Brennan -- Many Thanks Gunjani Aural sex gives me an earie feeling Copy into Excel then Data>Text to Columns>Delimited by comma. Gord Dibben Excel M...

Can't find "Password to open" box in Excel 2000
I need to put a password on a workbook to stop unauthorized access but can't find "Password to open" box in to tools->options->general dialog box That's because it's not in Tools/Options/General in the Excel menu. You need to select File/Save As, then click Tools, General Options in the save as dialog. >-----Original Message----- >I need to put a password on a workbook to stop >unauthorized access but can't find "Password to open" box >in to tools->options->general dialog box >. > ...

Two IF Statements with two answers
In cell J9 I need a formula that would do the following: If G9="Yes" then F9, If G9="No" then -f9 Note: G9 has a drop down list with Yes, No answers. If there are no other alternatives that yes and no =IF(G9="Yes",F9,-F9) otherwise =IF(G9="Yes",F9,IF(G9="No",-F9,"") Regards, Peo Sjoblom "heater" wrote: > In cell J9 I need a formula that would do the following: > > If G9="Yes" then F9, If G9="No" then -f9 > > Note: G9 has a drop down list with Yes, No answers. > =IF(G9=&qu...

Address book access problem
I have migrated my old vista laptop to my new Windows 7 laptop and can not get contacts to show up in the address area. Need help. -- Pecan Sorry, but this newsgroup is for questions about Access, the database product that's part of Office Professional. You'd be best off reposting to a newsgroup related to whatever e-mail client you're using, or one related to Windows. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "pecan@bigpond.com" <pecanbigpondcom@discussions.microsoft.com> wrote in message ne...

Copy Word Text into Excell
Good Morning, I have an Excell workbook that other individuals access and they need to put a response at the bottom. However, when they attempt to copy their response from a word document, it either won't accept it as the cells aren't the same, or it continuously goes across the page. I tried merging cells, but that didn't work. How can I accomplish this? Thanks, Deb One thing that may simplify the data entered is to use the Edit->Paste Special menu, and select "Text". That won't warranty that the data will be in the right cells, but at least the format...

WinCrypt problem
Hi! I'm trying to encrypt a password in my application. I use WinCrypt, but it doesn't work on any computer except mine. I used it on pc with Windows Xp and also mine OS is Windows XP. When I include WinCrypt, I write this code: //---------CODE #define _CRYPT32_ #define _WIN32_WINNT 0x0500 #include <wincrypt.h> //-----------END CODE On my computer is installated Net Framework, but not on the others I tried. Any ideas? Thanks Giulio "Mails" <mitticoo@tiscali.it> wrote in message news:1144702304.240372.176110@j33g2000cwa.googlegroups.com... > Hi! > I&...

Two axis
Positioning the Vertical axes: I have a chart where there are two axes. The first implementation produced the two scales on the right side, one inside, the other outside the chart area. Unusual, but luckily it fits well the chart. I tried to duplicate the feat with another dataset (similar in structure) and did not even get the second scale to show right, left or center (data plotted with two different scales allright). Where is the info to control these functionalities? Tx, Pierre On Wed, 29 Oct 2003 17:48:13 +0100, pl.carry <pl.carry@wanadoo.fr> wrote= : > Positioning the Vertic...