hi guys, ive been trying to get some help and i already tried going to other
groups and didnt get an answer that i understood and now my question seems to
be lost in the wind ;o) i hope someone here can help me. i have an exel sheet
that has all kinds of data in it. the sheet was a conversion from a PDF file.
it was a phone bill. what i need to do is find the fastest way to "clear" or
"delete" all the data and text in the sheet "except" the 10 digit phone
numbers. remember its a phone bill so there are many different phone numbers
but all have the "-" in the number like this 000-000-0000
ive hear of ways to clear and or delete everything but colums or rows with
"-" in them but i cant figure out how to do that. i dont know how to use
macros, and formulas and VB i just want to clear everything but the phone
numbers. i have 75 sheets to complete so thats why i need the fastest way,
thanks guys!
-Tina

Hi!

Still working on this?

Send me a copy of your file. NOT the PDF file, the XLS file. Is it 75
sheets? Just send 1 sheet.

XL IS NO help at comcast period net

Don't need IS NO and you should be able to figure out the rest.

Biff

"Tina Jones" wrote in message
...
hi guys, ive been trying to get some help and i already tried going to
other
groups and didnt get an answer that i understood and now my question seems
to
be lost in the wind ;o) i hope someone here can help me. i have an exel
sheet
that has all kinds of data in it. the sheet was a conversion from a PDF
file.
it was a phone bill. what i need to do is find the fastest way to "clear"
or
"delete" all the data and text in the sheet "except" the 10 digit phone
numbers. remember its a phone bill so there are many different phone
numbers
but all have the "-" in the number like this 000-000-0000
ive hear of ways to clear and or delete everything but colums or rows with
"-" in them but i cant figure out how to do that. i dont know how to use
macros, and formulas and VB i just want to clear everything but the phone
numbers. i have 75 sheets to complete so thats why i need the fastest way,
thanks guys!
-Tina

Select the cells on each sheet that contains the numbers
Add a new sheet & call it UniquePhoneNos
Open VB (Alt+F11)
Add the macro below to any sheet / module
Press F5 - jobs done.

Public Sub FindPhoneNos()
Dim rResults As Range, sh As Worksheet
Dim rTest As Range
Dim dic, key
Dim OutputSheet As Worksheet
Set dic = CreateObject("Scripting.Dictionary")
Set OutputSheet = Sheets("UniquePhoneNos")

'*******Get Phone Nos********

'loop through sheets
For Each sh In ThisWorkbook.Sheets
'check we are not looking at this sheet
If sh.Name < OutputSheet.Name And _
sh.Visible = xlSheetVisible Then
'Loop through selected cells
sh.Select
For Each rTest In Selection
'Check see if it is formated as ###-###-###
If rTest.Text Like "???-???-???" Then
'Test see if number has been found already
If dic.Exists(rTest.Text) Then
'YES - inc call count
dic(rTest.Text) = dic(rTest.Text) + 1
Else
'NO - set call count to 1
dic(rTest.Text) = 1
End If
End If
Next
End If
Next

'*******List Results********
'Clear output sheet
OutputSheet.Range("A1", "B65000").ClearContents

'Set rResults to start output at A1
Set rResults = OutputSheet.Range("A1")

'Set up titles
rResults.Value = "Phone Number"
rResults.Offset(0, 1).Value = "Call Count"

'Set rResults to start output at A2
Set rResults = OutputSheet.Range("A2")



For Each key In dic.Keys
'Set cell to Text Format
rResults.NumberFormat = "@"
'Put Phone Number in cell
rResults.Value = key

'Set cell to Text Format
rResults.Offset(0, 1).NumberFormat = "@"
'Put call count in cell
rResults.Offset(0, 1).Value = dic(key)

'refernece next cell (1 row down)
Set rResults = rResults.Offset(1, 0)
Next

'Show results
OutputSheet.Activate
End Sub


let me know if you get stuck.

"Tina Jones" wrote in message
...
hi guys, ive been trying to get some help and i already tried going to other
groups and didnt get an answer that i understood and now my question seems to
be lost in the wind ;o) i hope someone here can help me. i have an exel sheet
that has all kinds of data in it. the sheet was a conversion from a PDF file.
it was a phone bill. what i need to do is find the fastest way to "clear" or
"delete" all the data and text in the sheet "except" the 10 digit phone
numbers. remember its a phone bill so there are many different phone numbers
but all have the "-" in the number like this 000-000-0000
ive hear of ways to clear and or delete everything but colums or rows with
"-" in them but i cant figure out how to do that. i dont know how to use
macros, and formulas and VB i just want to clear everything but the phone
numbers. i have 75 sheets to complete so thats why i need the fastest way,
thanks guys!
-Tina

biff thank you again for posting. i tried to emai you and i got an error that
the email is not valid, yes i have a one sheet example. where can i send it?

"Biff" wrote:

Hi!

Still working on this?

Send me a copy of your file. NOT the PDF file, the XLS file. Is it 75
sheets? Just send 1 sheet.

XL IS NO help at comcast period net

Don't need IS NO and you should be able to figure out the rest.

Biff

"Tina Jones" wrote in message
...
hi guys, ive been trying to get some help and i already tried going to
other
groups and didnt get an answer that i understood and now my question seems
to
be lost in the wind ;o) i hope someone here can help me. i have an exel
sheet
that has all kinds of data in it. the sheet was a conversion from a PDF
file.
it was a phone bill. what i need to do is find the fastest way to "clear"
or
"delete" all the data and text in the sheet "except" the 10 digit phone
numbers. remember its a phone bill so there are many different phone
numbers
but all have the "-" in the number like this 000-000-0000
ive hear of ways to clear and or delete everything but colums or rows with
"-" in them but i cant figure out how to do that. i dont know how to use
macros, and formulas and VB i just want to clear everything but the phone
numbers. i have 75 sheets to complete so thats why i need the fastest way,
thanks guys!
-Tina

thank you so much but i have no idea what alot of that stuff means!

"Stevie_mac" wrote:

Select the cells on each sheet that contains the numbers
Add a new sheet & call it UniquePhoneNos
Open VB (Alt+F11)
Add the macro below to any sheet / module
Press F5 - jobs done.

Public Sub FindPhoneNos()
Dim rResults As Range, sh As Worksheet
Dim rTest As Range
Dim dic, key
Dim OutputSheet As Worksheet
Set dic = CreateObject("Scripting.Dictionary")
Set OutputSheet = Sheets("UniquePhoneNos")

'*******Get Phone Nos********

'loop through sheets
For Each sh In ThisWorkbook.Sheets
'check we are not looking at this sheet
If sh.Name < OutputSheet.Name And _
sh.Visible = xlSheetVisible Then
'Loop through selected cells
sh.Select
For Each rTest In Selection
'Check see if it is formated as ###-###-###
If rTest.Text Like "???-???-???" Then
'Test see if number has been found already
If dic.Exists(rTest.Text) Then
'YES - inc call count
dic(rTest.Text) = dic(rTest.Text) + 1
Else
'NO - set call count to 1
dic(rTest.Text) = 1
End If
End If
Next
End If
Next

'*******List Results********
'Clear output sheet
OutputSheet.Range("A1", "B65000").ClearContents

'Set rResults to start output at A1
Set rResults = OutputSheet.Range("A1")

'Set up titles
rResults.Value = "Phone Number"
rResults.Offset(0, 1).Value = "Call Count"

'Set rResults to start output at A2
Set rResults = OutputSheet.Range("A2")



For Each key In dic.Keys
'Set cell to Text Format
rResults.NumberFormat = "@"
'Put Phone Number in cell
rResults.Value = key

'Set cell to Text Format
rResults.Offset(0, 1).NumberFormat = "@"
'Put call count in cell
rResults.Offset(0, 1).Value = dic(key)

'refernece next cell (1 row down)
Set rResults = rResults.Offset(1, 0)
Next

'Show results
OutputSheet.Activate
End Sub


let me know if you get stuck.

"Tina Jones" wrote in message
...
hi guys, ive been trying to get some help and i already tried going to other
groups and didnt get an answer that i understood and now my question seems to
be lost in the wind ;o) i hope someone here can help me. i have an exel sheet
that has all kinds of data in it. the sheet was a conversion from a PDF file.
it was a phone bill. what i need to do is find the fastest way to "clear" or
"delete" all the data and text in the sheet "except" the 10 digit phone
numbers. remember its a phone bill so there are many different phone numbers
but all have the "-" in the number like this 000-000-0000
ive hear of ways to clear and or delete everything but colums or rows with
"-" in them but i cant figure out how to do that. i dont know how to use
macros, and formulas and VB i just want to clear everything but the phone
numbers. i have 75 sheets to complete so thats why i need the fastest way,
thanks guys!
-Tina

Which address did you use?

Decipher this one:

XL IS NO help at comcast period net

Don't need IS NO and you should be able to figure out the rest.

Biff

"Tina Jones" wrote in message
...
biff thank you again for posting. i tried to emai you and i got an error
that
the email is not valid, yes i have a one sheet example. where can i send
it?

"Biff" wrote:

Hi!

Still working on this?

Send me a copy of your file. NOT the PDF file, the XLS file. Is it 75
sheets? Just send 1 sheet.

XL IS NO help at comcast period net

Don't need IS NO and you should be able to figure out the rest.

Biff

"Tina Jones" wrote in message
...
hi guys, ive been trying to get some help and i already tried going to
other
groups and didnt get an answer that i understood and now my question
seems
to
be lost in the wind ;o) i hope someone here can help me. i have an exel
sheet
that has all kinds of data in it. the sheet was a conversion from a PDF
file.
it was a phone bill. what i need to do is find the fastest way to
"clear"
or
"delete" all the data and text in the sheet "except" the 10 digit phone
numbers. remember its a phone bill so there are many different phone
numbers
but all have the "-" in the number like this 000-000-0000
ive hear of ways to clear and or delete everything but colums or rows
with
"-" in them but i cant figure out how to do that. i dont know how to
use
macros, and formulas and VB i just want to clear everything but the
phone
numbers. i have 75 sheets to complete so thats why i need the fastest
way,
thanks guys!
-Tina

Hi!

If Tina sends me her file I'll run your macro on it and let you know how it
worked.

Biff

"Stevie_mac" wrote in message
...
Select the cells on each sheet that contains the numbers
Add a new sheet & call it UniquePhoneNos
Open VB (Alt+F11)
Add the macro below to any sheet / module
Press F5 - jobs done.

Public Sub FindPhoneNos()
Dim rResults As Range, sh As Worksheet
Dim rTest As Range
Dim dic, key
Dim OutputSheet As Worksheet
Set dic = CreateObject("Scripting.Dictionary")
Set OutputSheet = Sheets("UniquePhoneNos")

'*******Get Phone Nos********

'loop through sheets
For Each sh In ThisWorkbook.Sheets
'check we are not looking at this sheet
If sh.Name < OutputSheet.Name And _
sh.Visible = xlSheetVisible Then
'Loop through selected cells
sh.Select
For Each rTest In Selection
'Check see if it is formated as ###-###-###
If rTest.Text Like "???-???-???" Then
'Test see if number has been found already
If dic.Exists(rTest.Text) Then
'YES - inc call count
dic(rTest.Text) = dic(rTest.Text) + 1
Else
'NO - set call count to 1
dic(rTest.Text) = 1
End If
End If
Next
End If
Next

'*******List Results********
'Clear output sheet
OutputSheet.Range("A1", "B65000").ClearContents

'Set rResults to start output at A1
Set rResults = OutputSheet.Range("A1")

'Set up titles
rResults.Value = "Phone Number"
rResults.Offset(0, 1).Value = "Call Count"

'Set rResults to start output at A2
Set rResults = OutputSheet.Range("A2")



For Each key In dic.Keys
'Set cell to Text Format
rResults.NumberFormat = "@"
'Put Phone Number in cell
rResults.Value = key

'Set cell to Text Format
rResults.Offset(0, 1).NumberFormat = "@"
'Put call count in cell
rResults.Offset(0, 1).Value = dic(key)

'refernece next cell (1 row down)
Set rResults = rResults.Offset(1, 0)
Next

'Show results
OutputSheet.Activate
End Sub


let me know if you get stuck.

"Tina Jones" wrote in message
...
hi guys, ive been trying to get some help and i already tried going to
other
groups and didnt get an answer that i understood and now my question
seems to
be lost in the wind ;o) i hope someone here can help me. i have an exel
sheet
that has all kinds of data in it. the sheet was a conversion from a PDF
file.
it was a phone bill. what i need to do is find the fastest way to "clear"
or
"delete" all the data and text in the sheet "except" the 10 digit phone
numbers. remember its a phone bill so there are many different phone
numbers
but all have the "-" in the number like this 000-000-0000
ive hear of ways to clear and or delete everything but colums or rows
with
"-" in them but i cant figure out how to do that. i dont know how to use
macros, and formulas and VB i just want to clear everything but the phone
numbers. i have 75 sheets to complete so thats why i need the fastest
way,
thanks guys!
-Tina

biff, thank you, thank you, thank you. how do i send it to you? i tried
clicking your user name and sending an email to you but i got a return
receipt? please let me know. you can email me at mezmerized88-hotmail
thank you so much again!

"Biff" wrote:

Hi!

If Tina sends me her file I'll run your macro on it and let you know how it
worked.

Biff

"Stevie_mac" wrote in message
...
Select the cells on each sheet that contains the numbers
Add a new sheet & call it UniquePhoneNos
Open VB (Alt+F11)
Add the macro below to any sheet / module
Press F5 - jobs done.

Public Sub FindPhoneNos()
Dim rResults As Range, sh As Worksheet
Dim rTest As Range
Dim dic, key
Dim OutputSheet As Worksheet
Set dic = CreateObject("Scripting.Dictionary")
Set OutputSheet = Sheets("UniquePhoneNos")

'*******Get Phone Nos********

'loop through sheets
For Each sh In ThisWorkbook.Sheets
'check we are not looking at this sheet
If sh.Name < OutputSheet.Name And _
sh.Visible = xlSheetVisible Then
'Loop through selected cells
sh.Select
For Each rTest In Selection
'Check see if it is formated as ###-###-###
If rTest.Text Like "???-???-???" Then
'Test see if number has been found already
If dic.Exists(rTest.Text) Then
'YES - inc call count
dic(rTest.Text) = dic(rTest.Text) + 1
Else
'NO - set call count to 1
dic(rTest.Text) = 1
End If
End If
Next
End If
Next

'*******List Results********
'Clear output sheet
OutputSheet.Range("A1", "B65000").ClearContents

'Set rResults to start output at A1
Set rResults = OutputSheet.Range("A1")

'Set up titles
rResults.Value = "Phone Number"
rResults.Offset(0, 1).Value = "Call Count"

'Set rResults to start output at A2
Set rResults = OutputSheet.Range("A2")



For Each key In dic.Keys
'Set cell to Text Format
rResults.NumberFormat = "@"
'Put Phone Number in cell
rResults.Value = key

'Set cell to Text Format
rResults.Offset(0, 1).NumberFormat = "@"
'Put call count in cell
rResults.Offset(0, 1).Value = dic(key)

'refernece next cell (1 row down)
Set rResults = rResults.Offset(1, 0)
Next

'Show results
OutputSheet.Activate
End Sub


let me know if you get stuck.

"Tina Jones" wrote in message
...
hi guys, ive been trying to get some help and i already tried going to
other
groups and didnt get an answer that i understood and now my question
seems to
be lost in the wind ;o) i hope someone here can help me. i have an exel
sheet
that has all kinds of data in it. the sheet was a conversion from a PDF
file.
it was a phone bill. what i need to do is find the fastest way to "clear"
or
"delete" all the data and text in the sheet "except" the 10 digit phone
numbers. remember its a phone bill so there are many different phone
numbers
but all have the "-" in the number like this 000-000-0000
ive hear of ways to clear and or delete everything but colums or rows
with
"-" in them but i cant figure out how to do that. i dont know how to use
macros, and formulas and VB i just want to clear everything but the phone
numbers. i have 75 sheets to complete so thats why i need the fastest
way,
thanks guys!
-Tina