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

"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
---

"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
---

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" wrote in message
...

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

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

"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
---