?
Solved

MACRO FOR EXCEL

Posted on 2005-05-06
60
Medium Priority
?
873 Views
Last Modified: 2011-10-03
i have no excel knowlede AT ALL. i know how to cut, paste and run a macro!!
i have a spreadsheet that shows phone numbers in each row based on the month in each colum. the months are listed in the header as follows JAN - FEB FEB - MAR, ect, ect. (colums A-L) each row has a different count of numbers because the activity was different. one month i made 360 calls and another 410 calls, got it?

1. i want to run a macro or module that will delete everything but my (209) area code numbers. i have the value set at (555) 555-5555 BUT i can change it to 555-555-5555 or 5555555555. it dont matter to me whatever makes the macro eaiser if anything.  

2. and if at all possible i would like to run something that will "delete, clear or remove" duplicates.

thank you in advance, if you could just give me the macro all i plan to do is cut/paste cause i dont know much more ;o)
0
Comment
Question by:jaysonmichaels
  • 29
  • 27
  • 2
  • +2
60 Comments
 
LVL 37

Expert Comment

by:Harisha M G
ID: 13947067
Hi jaysonmichaels,

Sub Remover()
    Dim cel As Range
    For Each cel In Sheet1.[A2:L400]    ' <---- Change the range and sheet as required
        cel.Replace "(*)-", "", XlLookAt.xlPart
    Next
End Sub



Bye
---
Harish
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 13947099
jaysonmichaels,
    Goto Excel --> Tools --> Macro --> Visual Basic Editor.

    Then goto Insert --> Module.

    Paste this code, change the range as per your requirement.
    ...Note that there should not be - after ) in the above code...
   

Sub Remover()
    Dim cel As Range
    For Each cel In Sheet1.[A2:L400]    ' <---- Change the range and sheet as required
        cel.Replace "(*) ", "", XlLookAt.xlPart
    Next
End Sub
0
 
LVL 3

Assisted Solution

by:LFLFM
LFLFM earned 80 total points
ID: 13947560
Here is a Macro that will clear duplicates:
First, how to use:
It works by columns, since you have each month on a column, select the numbers where you want to clear the dups, then run the macro:
'-----
Sub cleardups()
    Application.ScreenUpdating = False
    Rc = Selection.Rows.Count
    For i = Rc To 1 Step -1
        CheckVal = ActiveCell
        ActiveCell.Offset(1, 0).Select
        j = 1
        For j = 1 To i
            If ActiveCell = CheckVal Then
                Selection.Clear
            End If
            ActiveCell.Offset(1, 0).Select
        Next j
        ActiveCell.Offset(-i, 0).Select
    Next i
    Application.ScreenUpdating = True
End Sub
'-----
Note DON'T select the entire column. This macro will crash (because of excel's 65536 rows limit). I don't have time right now to make the macro support full column selection, sorry =)

Here's the one to clear everything but "(209)":

Sub clearno209()
    Application.ScreenUpdating = False
    rc = Selection.Rows.Count
    For i = 1 To rc Step 1
        If Not (ActiveCell Like "(209)*") Then
            ActiveCell.Clear
        End If
        ActiveCell.Offset(1, 0).Select
    Next i
    Application.ScreenUpdating = True
End Sub

Same thing, don't use this on the entire column. =D

Hope this helps =D
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jaysonmichaels
ID: 13948080
what do you mean run it by colum? example i have colums a-l and like i said some go down as far as 850. so i would select "highlight" colum A and then open VB and run macro?
0
 

Author Comment

by:jaysonmichaels
ID: 13948184
ATTN USER: "LFLFM"

i followed your instructions, even remove the - after ) and its not working. any ideas? the name of my excel book is "master" and it has two sheets in it "sheet 1" and "details" spelled just like that case sen and no ""
0
 

Author Comment

by:jaysonmichaels
ID: 13948210
SORRY SORRY MY ABOVE COMMENT WAS FOR USER MG MGHARISH! NOT LFLFM

ATTN;LFLFM
i get an error when i run the cleardups. here is the error
Run Time error '1004': application -defined or object -defined error
"END"   "DEBUG"   OR   "HELP"

i have not tried the clear209 yet.


ATTN MG MGHARISH
i followed your instructions, even remove the - after ) and its not working. any ideas? the name of my excel book is "master" and it has two sheets in it "sheet 1" and "details" spelled just like that case sen and no ""
0
 

Author Comment

by:jaysonmichaels
ID: 13948219
WAIT SORRY MG MGHARISH. i got way ahead of myself and did what you asked me not to do, select the entire colum! sorry, sorry i am not worthy! let me try again!
0
 

Author Comment

by:jaysonmichaels
ID: 13948232
ok MG MGHARISH i selected colum A rows 2-7694 ran the macro and everything deleted ;) does that macro require the numbers to be in a certain format like (555) 555-5555, 555-555-5555 or 5555555555
0
 

Assisted Solution

by:jsittko
jsittko earned 80 total points
ID: 13948557
My advice:

Since you know no Excel.

I recommend simply sorting each column (sounds like you'll have to sort 12 times (12 months).)
Then delete the cells above and below those you do not want.

This may not be elegant BUT I am an expert Excel user and this simple task is easier via the simple sort delete than using a macro.  Indeed, there are cool methods to get this done but sometimes a cool algorythm does not warrant the time spent for the task.  Moreover, since you know nothing, how are you going to mount this macro?  That spreadsheet only?  If not, how are you going to call it for other spreadsheets?  Know how to make an icon for the toolbar and attach it to a module?

I myself would use the sort, delete method for this.  The cumulative time saved will not be faster than my typing and mousing.
0
 

Author Comment

by:jaysonmichaels
ID: 13948593
HAHHA ya i was going to try that BUT what you dont know is i have 75 bills. 12 months per bill ;o)
0
 
LVL 57

Assisted Solution

by:giltjr
giltjr earned 160 total points
ID: 13948782
Open your spread sheet.

If your spread sheet does NOT have a header row, insert a new row at the very top.

On the tool bar select Data, then Filter, then autofilter.

Now you should get little arrows on the right side of each row.  On the row with the phone number press the down arrow and select custom.  In the box that has equals, press the down arrow and scroll until you see begins with.  Then in the box to the right of that, put in your area code, if the data has the parens, include them.  Press O.K. and only the rows with your area code should exist.  You can now select everything and copy and past it.
0
 
LVL 57

Assisted Solution

by:giltjr
giltjr earned 160 total points
ID: 13948979
One other question.  Does the file actually start off as an Excel Spreadsheet?  Or does it start off as a sequential file that is delimited somehow?

If so you could use the find command to filter out the number with your area code.  Or you could save the spreadsheet  in csv format, use the find command to filter out the data you need.
0
 

Author Comment

by:jaysonmichaels
ID: 13949452
its was a PDF to excel conversion
0
 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 1680 total points
ID: 13952826
jaysonmichaels,
    You told my code was not working....

    I have attached a Excel sheet...

    http://geocities.com/mgh_mgharish/jaysonmichaels.xls
    To check, Goto Excel --> Tools --> Macro --> Macros... Select Remover and click Run
   
    To insert the code in your workbook,
    Goto Excel --> Tools --> Macro --> Visual Basic Editor.

    Then goto Insert --> Module.   Paste the below code... with necessary changes in sheet and range.
__________________________________________________________________________________________

Sub Remover()
    Sheets("Sheet1").Range("A2:L400").Replace "(*) ", "", XlLookAt.xlPart
End Sub
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
0
 

Author Comment

by:jaysonmichaels
ID: 13953587
mgh mgharish, i didnt mean to offend you in anyway. what i meant was when i run your macro nothing on my workbook changes. i did use yours and the only thing it did was delete the area codes. lets try this if you dont mind, this is a sample. i would never expect someone to do my work for me so i have created a sample. you can DL it from:

http://www.jaysondecambra.com/locker2
its called "test.xls"

here is what i need to do, i need to delete all the duplicate numbers and keep them in the same colum so i know what month(s) each number was dialed. most of the numbers are all over the book, in every month. i just need to get rid of the dups if you know what i mean. i cant offer you pay for your time due to rules, so i would really apreciate your help.
thanks again!
0
 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 1680 total points
ID: 13954342
I am not hurted.. don't worry !!

No problem... I misunderstood your problem.. sorry.. I thought you needed to delete area codes....

And this code will remove everything except the area codes...
and then deletes the repeated records and places only the non-duplicate entries...

Sub A()
    On Error Resume Next
    Dim cel As Range
    Application.ScreenUpdating = False
    For Each cel In Sheets("TEST").Range("A2:L427")
        If cel.Text = "" Then GoTo Nxt
       
        cel.Value = Replace(Replace(Replace(cel.Text, Mid(cel.Text, InStr(1, cel.Text, " ")), ""), "(", ""), ")", "")
        cel.NumberFormat = ""
Nxt:
    Next
   
    For Each cel In Sheets("TEST").Range("A1:L1")
        Range(cel, cel.End(xlDown)).AdvancedFilter xlFilterCopy, , cel.Offset(, 12), True
    Next
   
    x = Range("A:L").ColumnWidth
    Range("A:L").Delete
    Range("A:L").ColumnWidth = x
    Application.ScreenUpdating = True
End Sub





0
 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 1680 total points
ID: 13954499
 Be sure to change the range...                 here
                                                                 VVV
   For Each cel In Sheets("TEST").Range("A2:L427")
 
0
 

Author Comment

by:jaysonmichaels
ID: 13954504
ok i just want to clear up something you said in your last comment above. did you say the new macro will "delete everything but the area codes"remove everything except the area codes and then..

i dont want the area codes deleted at all. i guess i need "two" macros. the first macro i would like to delete all the duplicate phone numbers, not just the area code, the entire phone number BUT it needs to leave one left so i know the number was dialed that billing period. if i need to put that "one" in the header i will if its possibe. once thats done i will take note of the phone numbers.

next i want to delete lets say all the (925) phon numbers so i can take note of everything except the (925) numbers. then rerun it and delete lets say all the (415) numbers so i can take note of all the other numbers, get it? remember i am no good at excel this is just creative thinking. i dont know if its possible or alot eaiser than im making it to be. i know i need to run two macros at two different time. here let me explain it this way. if you were able to take a look at the test one i made here would be my goal, after all is said and done i want to know how many different phone numbers i called in what month. i want to create a list of all my numbers by area code for each month.

thanks again man, you've been great!
if i can give out my email, ill give it to you. i dont know the rules about that?

0
 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 1680 total points
ID: 13954523
If you have checked the above code.. It will delete all the numbers without area codes. Then it will remove duplicates.

You can have the email here
http://www.experts-exchange.com/M_3530577.html

Click Edit.. and change your profile to show email id...

Mine is
http://www.experts-exchange.com/M_2198666.html


Well... first check the code and suggest what changes you want
0
 

Author Comment

by:jaysonmichaels
ID: 13954534
i tried that code and everything gets deleted. i have one more idea sir, i am going to upload a sample of what i would like if at all possible my sheet to look like when done. give me about 5mins to upload it, the link will be the same the xls will be called "FINISHED"
THANKS! again!
0
 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 1680 total points
ID: 13954551
Here is the file you provided along with my code...

Press Alt + F8.
Select the macro..
Click Run

Only the area codes will remain...

If that is not what you want, please send a (small) xl file which shows what output you should get
0
 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 1680 total points
ID: 13954562
> If that is not what you want, please send a (small) xl file which shows what output you should get
OK.. you have already thought of that
0
 

Author Comment

by:jaysonmichaels
ID: 13954581
ok done. one more thing, lets FORGET about the area code issue. i dont know what i was thinking, i can get rid of the area codes after all the dups are gone! lets just work on getting rid of all the dups ;o)
thank you thank you thank you  again!
0
 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 1680 total points
ID: 13954603
That is what my code is doing...

http://geocities.com/mgh_mgharish/test.xls
0
 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 1680 total points
ID: 13954661
I am still in doubt ... whether you want

1) Area codes without phone number
2) Phone number without area code
3) Need both
0
 

Author Comment

by:jaysonmichaels
ID: 13954679
ok may be loosing my mind, im just now getting to my coffee so bare with me. i think we are not on the same page and its my fault since i know nothing about excel ;o)
when i open your version of the test i see all the numbers have just been sorted so they are all together, i mean this makes it easy to delete the dups but i was hoping the macro would delete them so when i go back to the sheet the numbers are only listed once "in each colum" if im missing something i am sorry for all the trouble.
thanks again!
0
 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 1680 total points
ID: 13954708
Let us forget the phone numbers....
 
Consider you have
          A           B           C           D
         Jan        Feb        Mar       Apr
          1            3           5           1
          2            1           3           3
          5            3           7           1
          1            3           4           1

 And you want
          A           B           C           D
         Jan        Feb        Mar       Apr
          1            3           5           1
          2            1           3           3
          5                         7          
                                     4          


to be the output...

Right ??
0
 

Author Comment

by:jaysonmichaels
ID: 13954715
ok im sorry, lets forget about the area code thing, i made that confusing. so i want to keep all area codes, all phone number "except" the duplicate phone numbers. lets say in colum A jan-feb 04 i called 510-555-1212 150 times and in colum B i called 510-555-1212 268 times, in colum C i called 510-555-1212 368 times.
when all is said and done id like to see 510-555-1212 listed 1 time in A (149 were duplicates removed) 1 time in colum B (267 were removed) 1 time in C (367 were removed)
does that make more scense?
0
 

Author Comment

by:jaysonmichaels
ID: 13954720
Let us forget the phone numbers....
 
Consider you have
          A           B           C           D
         Jan        Feb        Mar       Apr
          1            3           5           1
          2            1           3           3
          5            3           7           1
          1            3           4           1

 And you want
          A           B           C           D
         Jan        Feb        Mar       Apr
          1            3           5           1
          2            1           3           3
          5                         7          
                                     4          


to be the output...

Right ??



YES YES YES YES YES YES YES YES YESY EYSYEYSYEYEWYEYSYESYEYSYESY
YEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEESSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS

;o)
0
 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 1680 total points
ID: 13954755
Yes that is what my code is doing... I don't know how you are testing....


    On Error Resume Next                      '<--- To avoid errors
    Dim cel As Range                               '<--- Declaration
    Application.ScreenUpdating = False    '<--- Avoid screen flickering while running the code

    For Each cel In Sheets("TEST").Range("A1:L1")        '<--- Loop through each column
        Range(cel, cel.End(xlDown)).AdvancedFilter xlFilterCopy, , cel.Offset(, 12), True        '<--- Filter for unique
    Next                                                '<--- End syntax for For Loop
   
    x = Range("A:L").ColumnWidth            '<--- Save the column width              
    Range("A:L").Delete                              '<--- Delete original entries
    Range("A:L").ColumnWidth = x            '<--- Set the column width to original size
    Application.ScreenUpdating = True        '<--- Loop through each column
0
 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 1680 total points
ID: 13954768
1) Enter a sample
2) Press Alt + F11
3) Insert --> Module
4) Paste the above code within some procedure like....

Sub mymacro()
    On Error Resume Next                      '<--- To avoid errors
    Dim cel As Range                               '<--- Declaration
    Application.ScreenUpdating = False    '<--- Avoid screen flickering while running the code

    For Each cel In Sheets("TEST").Range("A1:L1")        '<--- Loop through each column
        Range(cel, cel.End(xlDown)).AdvancedFilter xlFilterCopy, , cel.Offset(, 12), True        '<--- Filter for unique
    Next                                                '<--- End syntax for For Loop
   
    x = Range("A:L").ColumnWidth            '<--- Save the column width              
    Range("A:L").Delete                              '<--- Delete original entries
    Range("A:L").ColumnWidth = x            '<--- Set the column width to original size
    Application.ScreenUpdating = True        '<--- Loop through each column
End Sub

5) Press Alt + F11 to return to Excel
6) Press Alt + F8
7) Select mymacro
8) Run.

If you do these steps exactly as stated, you MUST get the results
0
 

Author Comment

by:jaysonmichaels
ID: 13954798
ok maybe thats what i am doing wrong, i was just copy and paste your code! so your saying that i have to edit your macro?
this is what i was going to copy, paste and run! i was not going to change anything! i didnt think i had too!

Sub mymacro()
    On Error Resume Next                      '<--- To avoid errors
    Dim cel As Range                               '<--- Declaration
    Application.ScreenUpdating = False    '<--- Avoid screen flickering while running the code

    For Each cel In Sheets("TEST").Range("A1:L1")        '<--- Loop through each column
        Range(cel, cel.End(xlDown)).AdvancedFilter xlFilterCopy, , cel.Offset(, 12), True        '<--- Filter for unique
    Next                                                '<--- End syntax for For Loop
   
    x = Range("A:L").ColumnWidth            '<--- Save the column width              
    Range("A:L").Delete                              '<--- Delete original entries
    Range("A:L").ColumnWidth = x            '<--- Set the column width to original size
    Application.ScreenUpdating = True        '<--- Loop through each column
End Sub
0
 
LVL 37

Accepted Solution

by:
Harisha M G earned 1680 total points
ID: 13954853
No...  you don't have to edit any thing...
Which version of Excel are you using ?? I am using Office XP  (2002)

Do this EXACTLY

1) Open your test.xls (your first link)
2) Press Alt + F11 (This will take you to VBA)
3) Goto Insert menu and select Module (This will create a module that is shown in th Project Explorer window)
4) Paste this exactly.
___________________________________________
Sub mymacro()
    On Error Resume Next                    
    Dim cel As Range                        
    Application.ScreenUpdating = False    

    For Each cel In Sheets("TEST").Range("A1:L1")      
        Range(cel, cel.End(xlDown)).AdvancedFilter xlFilterCopy, , cel.Offset(, 12), True    
    Next                                            
   
    x = Range("A:L").ColumnWidth          
    Range("A:L").Delete                            
    Range("A:L").ColumnWidth = x          
    Application.ScreenUpdating = True    
End Sub
___________________________________________
5) Press Alt + F11 again (You will come back to Excel)
6) Press Alt + F8 (You will be taken to Macros window)
7) Select the macro.. mymacro from the list.
8) Run

All the duplicates are removed in test.xls

If this is true, then apply the same to your original file
0
 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 1680 total points
ID: 13954865
Or do this...

Open this file... http://geocities.com/mgh_mgharish/test.xls
Press Alt + F8
Select the macro A
Run.

All the duplicates get deleted. Am I right ??
0
 

Author Comment

by:jaysonmichaels
ID: 13954867
IT WORKED IT WORKED IT WORKED IT WORKED IT WORKED IT WORED


you are the man THANK you

wanna help with one more ;o) LMAO!
serious too, i promise this one will be cake walk cause i alreay had a macro for it, i just lost it!
if you want to get points for it i can set up a new question? what do you think???

ive uploaded my last request, the link is same as before xls is named "thankyou.xls"

what i want to do with that is DELETE everything except the phone numbers, as you can see there is alot of text in that sheet. there are also alot of numbers, 144, 000, 8954, ect BUT all the phone numbers are listed as (925) 555-1212. i just want the phone numbers i dont care where, how or if there are duplicates!!! i just want all the garbadge text and non phone numbers gone!
thanks again man!
0
 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 1680 total points
ID: 13954876
ive uploaded my last request, the link is same as before xls is named "thankyou.xls"

There is no link !!!
0
 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 1680 total points
ID: 13954883
Please upload properly
0
 

Author Comment

by:jaysonmichaels
ID: 13954896
i know im sorry my ftp crashed on me in the midldle of it, give me 5mins! sorry!
0
 

Author Comment

by:jaysonmichaels
ID: 13954937
ok its up, ill let you know that some of the spreadsheet i have, have alot more data (text) i was only able to covert one page over on the thankyou example, some of the pdfs im converting have 120 pages so the excel sheet has more colums and more rows. again i just want the phone numbers left INCLUDING dups
thanks again man!
0
 

Author Comment

by:jaysonmichaels
ID: 13954964
AND since im such a freeloader hehe j/k
with the first one you helped me with "the duplicate macro"

Sub mymacro()
    On Error Resume Next                    
    Dim cel As Range                        
    Application.ScreenUpdating = False    

    For Each cel In Sheets("TEST").Range("A1:L1")      
        Range(cel, cel.End(xlDown)).AdvancedFilter xlFilterCopy, , cel.Offset(, 12), True    
    Next                                            
   
    x = Range("A:L").ColumnWidth          
    Range("A:L").Delete                            
    Range("A:L").ColumnWidth = x          
    Application.ScreenUpdating = True    
End Sub


if i was able to get all the phone numbers into one colum (with duplicates) down to row 8985 what would i change?
if im learning i think i would change the ("A1:L1") to (A1:A8985") ???
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 13954976
No... If you are copying all the phone numbers to column A,

Just use Range("A1:A1")
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 13954999
What should I do with your new file ?
0
 

Author Comment

by:jaysonmichaels
ID: 13955216
could you give me a macro that would "delete" everything except the phone numbers?
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 13955225
Sure... But it will be day after tomorrow as the time here is 12:30 AM
and I want to sleeeep Zzzz..zzz
0
 

Author Comment

by:jaysonmichaels
ID: 13955246
anything man thanks again for all your help!
0
 

Author Comment

by:jaysonmichaels
ID: 13958546
where you going to take a nap or hybernate for the winter?
haha
just kiddin!
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 13970835
OK... In your file, the phone numbers are arbitrarily placed.. without any order.. Where should I look for them ?

Do you really nead to delete everything except phone numbers ??
0
 

Author Comment

by:jaysonmichaels
ID: 13971013
thats the thing, the numbers are kinda spread out. this document is a conversion from a PDF. when i convert over that is what i get. ALSO before i was turned onto experts exchange i did receive a macro from an excel help forum somewhere on the internet and it worked fine. there were a few things left behind that i had to clean up but it worked. i dont know where the macro is and i cant remember what forum i got it from. after you helped me i figured i would ask you.

YES i want everything deleted but phone numbers, however if for some reason thats not possible thats fine i can deal with it. here is my main objective because i have to pull numbers out everymonth so i wanted to get a system going that would help me and right now im catching up on the past 24 months 3 different phone bills ;o)

so like i was saying, i just want to delete as much as i can to pull the phone numbers out without the duplicates! since you have been a great deal of help i dont want to take up too much or your time so here is what ill do. i will create and "before" and "after" excel book so you can look at the two knowing what my goal is and tell me if its possible ;o) please give me about 15 mins to upload them

http://www.jaysondecambra.com/locker2

15mins!

thanks again buddy!
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 13971053
You say... you want to pull out the phone numbers...

Will it be okay if the phone numbers are pulled to new sheet?
0
 

Author Comment

by:jaysonmichaels
ID: 13971192
YES!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
0
 

Author Comment

by:jaysonmichaels
ID: 13971214
as you can see i dont know excel very well
0
 

Author Comment

by:jaysonmichaels
ID: 13971217
did you see the before and after?
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 13971359
Yes... but it is very difficult to get all the information like Type, date, time, phone number etc...

Do you want all those or only phone numbers ?
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 13971438
Open BEFORE book

1. Press Alt + F11 to goto VBA.
2. Insert --> Module.
3. Paste the code given below... and change the ranges as necessary
4. Press Alt + F11 again to come back to Excel.
5. Press Alt + F8 to goto macros dialog.
6. Select the macro and click Run

Sub a()
    On Error Resume Next
    Dim rw As Range
    Dim x As Range
    Dim i As Integer
    i = 1
    For Each rw In Sheet1.Range("A20:A100")
        Set x = rw.EntireRow.Find("*-*-*")
        If Not x Is Nothing Then
            MsgBox x.Row & " " & x.Column
            x.Select
            Sheet2.Cells(i, 1) = x.Offset(0, -5)
            Sheet2.Cells(i, 2) = x.Offset(0, -4)
            Sheet2.Cells(i, 3) = x.Offset(0, -3)
            Sheet2.Cells(i, 4) = x.Offset(0, -2)
            Sheet2.Cells(i, 5) = x
            i = i + 1
        End If
    Next
End SubSub a()
    On Error Resume Next
    Dim rw As Range
    Dim x As Range
    Dim i As Integer
    i = 1
    For Each rw In Sheet1.Range("A20:A100")
        Set x = rw.EntireRow.Find("*-*-*")
        If Not x Is Nothing Then
            MsgBox x.Row & " " & x.Column
            x.Select
            Sheet2.Cells(i, 1) = x.Offset(0, -5)
            Sheet2.Cells(i, 2) = x.Offset(0, -4)
            Sheet2.Cells(i, 3) = x.Offset(0, -3)
            Sheet2.Cells(i, 4) = x.Offset(0, -2)
            Sheet2.Cells(i, 5) = x
            i = i + 1
        End If
    Next
End Sub

__________
The output will be in Sheet2
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 13971463
Hi.. remove these lines...

            MsgBox x.Row & " " & x.Column
            x.Select

in both the loops...

And when you get the output in the second sheet, select the second column (B) and goto Format cells, and select Date and click OK. Similarly, for the third column (C) , set it to Time format
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 13971510
Sub a()
    On Error Resume Next
    Dim rw As Range
    Dim x As Range
    Dim i As Integer
    i = 1
    For Each rw In Sheet1.Range("A20:A100")
        Set x = rw.EntireRow.Find("*-*-*")
        If Not x Is Nothing Then
            Sheet2.Cells(i, 1) = x.Offset(0, -5)
            Sheet2.Cells(i, 2) = x.Offset(0, -4)
            Sheet2.Cells(i, 3) = x.Offset(0, -3)
            Sheet2.Cells(i, 4) = x.Offset(0, -2)
            Sheet2.Cells(i, 5) = x
            i = i + 1
        End If
    Next
    For Each rw In Sheet1.Range("A20:A100")
        Set x = rw.EntireRow.Find("Incoming")
        If Not x Is Nothing Then
            Sheet2.Cells(i, 1) = x.Offset(0, -5)
            Sheet2.Cells(i, 2) = x.Offset(0, -4)
            Sheet2.Cells(i, 3) = x.Offset(0, -3)
            Sheet2.Cells(i, 4) = x.Offset(0, -2)
            Sheet2.Cells(i, 5) = x
            i = i + 1
        End If
    Next
End Sub

0
 

Author Comment

by:jaysonmichaels
ID: 13972136
ok i tried the first one and took out what you asked and i got this error: compile error syntax error End SubSub a()
0
 

Author Comment

by:jaysonmichaels
ID: 13972210
NEVERMIND i have it working!
ok before i give you one hella of a thank you i have one last question?
the before sheet that i uploaded for you was only one page of the PDF. some PDF's are 41 pages so alot more data transfers over but its the same format, it looks just like the example i sent you but more rows. will this macro work with more rows with more data??
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 13972238
Yes... change the ranges....
A1:A10000
etc..
0
 

Author Comment

by:jaysonmichaels
ID: 13972431










YOU A R E THE MAN!
seriously thank you!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Want to know how to use Exchange Server Eseutil command? Go through this article as it gives you the know-how.
Steps to fix error: “Couldn’t mount the database that you specified. Specified database: HU-DB; Error code: An Active Manager operation fail”
Viewers will learn how to use the Hootsuite Dashboard.
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question