Two excel problems

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

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 said:
.. 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).
Click to expand...

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.

---
 
Tim said:
.. 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.
Click to expand...

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 said:
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.
Click to expand...


Worked perfectly thanks Maxs

Tim
 
Back
Top