Results 1 to 6 of 6

Thread: Moving bulk information into Excel

  1. #1

    Moving bulk information into Excel



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to help a colleague handle information that currently populates a 732 page PDF document, so my first thought was Excel. I saved the document into Excel format, but (a) roughly 50% of this transferred information is useless and (b) what I do want to work with dumps into one column more than 50,000 rows down. I have gone back to the original PDF and saved it as a Word document, so that I can edit out the useless information. This will leave me with the names and addresses that I am wanting to pull, but they all go into rows in the first column. Is there any way I can break them into 3 columns for every three rows? (Name, address, city - they currently look like an address label prior to being dumped into Excel)

    I am familiar with Excel basics - but don't know if what I am aiming for is even possible. If I could get this information into a spreadsheet to set up a Mail Merge, it would save my colleague many hours of mind numbing manual manipulation. Any ideas or suggestions gratefully recieved, thanks!

  2. #2
    Can you post an example of the useful data, and explain/show what it should look like. It looks like Text to Columns might help, but it would be good to see the data.

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    Can you post an example of the useful data, and explain/show what it should look like. It looks like Text to Columns might help, but it would be good to see the data.
    The data in my Word document looks like an address label, as below. When I save it as an Excel spreadsheet, all the information dumps into sequential rows. I would like to put this into three seperate columns for NAME, ADDRESS and CITY so that I can make a Mail Merge out of the data, but not sure how I can manipulate it without manually cutting and pasting it into the columns.

    Doe John James
    234 Blue Jay Way
    Campbell River, BC V9N 3Y7

    Friar Jane Bea
    746 Credential Crescent
    Campbell River, BC V9N 3Y7

    Bloe Frank William Bloe Shirley Leigh
    8464 Franklin Blvd
    Campbell River, BC V9N 3Y7

    Thanks!

  4. #4
    You could run this code on the data after you get it int Excel

    Code:
    Public Sub ProcessData()Dim lastrow As Long
    Dim i As Long
    
    
        Application.ScreenUpdating = False
        
        With ActiveSheet
        
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For i = lastrow To 1 Step -4
            
                .Cells(i, "A").Copy .Cells(i - 1, "B")
                .Cells(i - 1, "A").Resize(, 2).Copy .Cells(i - 2, "B")
                .Rows(i - 1).Resize(2).Delete
            Next i
        End With
        
        Application.ScreenUpdating = True
    End Sub

  5. #5
    I am learning on the fly to try and get something put together, so I may have done something wrong...I thought you must mean to run this as a VBA script macro, so I set it up to test it - but cannot get it to run. It keeps getting a syntax error on the first line [Public Sub ProcessData()Dim lastrow As Long] and I'm not certain if this is because my macro name shows up in the very start > Sub MacroName, then the script underneath. It was fun learning to run VBA macro scripts, but I don't know enough to troubleshoot them - yet

    Thanks

    Quote Originally Posted by Bob Phillips View Post
    You could run this code on the data after you get it int Excel

    Code:
    Public Sub ProcessData()Dim lastrow As Long
    Dim i As Long
    
    
        Application.ScreenUpdating = False
        
        With ActiveSheet
        
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For i = lastrow To 1 Step -4
            
                .Cells(i, "A").Copy .Cells(i - 1, "B")
                .Cells(i - 1, "A").Resize(, 2).Copy .Cells(i - 2, "B")
                .Rows(i - 1).Resize(2).Delete
            Next i
        End With
        
        Application.ScreenUpdating = True
    End Sub

  6. #6
    That is because the code did not post properly. The code 'Dim lastrow As Long'​ should be on a line of its own.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •