Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Clean up data (2003)

    I'm working on a project to consolidate the contents of four regulations. Each regulation has its own glossary, and the consolidated glossary thus has many redundant entries. The regulations are in Word, so I copied each glossary and pasted them into a single Excel spreadsheet. The spreadsheet now displays the contents of the glossaries in one column (A). Cell A1 has the first word, Cell A2 the definition, and Cell A3 is blank. The second word appears in Cell A4...and so on and so on.

    I eventually want to import the entire list into Access, where I can use the Find Duplicates wizard. Before I import into Access, however, I think I need to do the following:

    1. Remove the empty rows, and
    2. Put the definitions into Column B.

    To delete the empty rows, I tried using the macro Rudi posted in <post#=419416>post 419416</post#> but without success. I confess I've no experience building macros in Excel. I've used the macro recorder, but naught else. I plugged Rudi's macro into something in the VBE, and now the macro appears in the list of macros at Tools > macros.... But when I run the macro, nothing happens. I don't get an error message

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clean up data (2003)

    Does this do what you want:

    <pre>Public Sub RegFormat()
    Dim I As Long, lLastRow As Long
    lLastRow = Range("A65536").End(xlUp).Row
    For I = lLastRow To 1 Step -3
    Range("B1").Offset(I - 2, 0).Value = Range("A1").Offset(I - 1, 0).Value
    Range(Range("A1").Offset(I - 1, 0), Range("A1").Offset(I, 0)).EntireRow.Delete
    Next I
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Lounger
    Join Date
    Nov 2004
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clean up data (2003)

    Doing it without macros (advantage - you can check each step.):
    Step 1. Insert Row and use row 1 for headings. Cell A1 becomes "Original"
    Step 2. In column B, heading is "Order". number 1, 2, 3, etc all the way down. So B2=1, B3=2, B4=3, B5=4, . This means you can sort and retrieve your original.

    (You can put cursor into cell a2 and choose Window FreezePanes, to keep the headings even when you scroll to the bottom of the data if you like.)
    Step 3. Select from A1 to end of B. Data Sort on column A (original). (Make sure you check the box saying your data have headings.) The blank rows go to the bottom. Re-select from A1 to the end of the data. sort on Column B (Order). It's back as before only without the blank rows.
    Step 4. In Cell C1, put the heading "Definition"
    Step 5. In Cell c2, put =a3 as the formula. Copy it to cell c4.
    Step 6. Select from cell c2 to cell c5 (the blank cell). Move the cursor to the bottom right corner of cell c5 so that it changes to a small + symbol and double click it. This should copy the formulae all the way down. Or drag it down to the bottom of the data. or copy the range c2..c5; select the range c2.."cell containing last name" and paste.
    You should now have the name and the definition beside each other with a blank row between.
    Step 7. Select the whole column c and copy and paste-as-values.
    Step 8. Select the entire set of data, from A1 .. end of data. Sort on column C (the definitions.) All the rows with definitions in column A will go to the bottom.
    Step 9. Select from A1 to end of column C and sort on "order".
    It should now be back in original order, with the name in column A, the order (1,4,7,...) in column B, and the definition in column C.
    Step 10, Delete column B and delete extraneous rows below the end of data. Delete row 1 to remove headings if you wish.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clean up data (2003)

    Just another hint:

    After doing either Ruth's or Legare's suggestions you don't need to use Access to get rid of duplicates.
    You can do Data, filter, advanced filter, select "copy to another location" and check the box "Unique entries only".
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Clean up data (2003)

    Legare: Thanks the for reply. I've started to suspect there's something wrong with the way I create a macro. I keyed in your code exactly as it appears in your post, but, as before, nothing happens when I try to run the macro. The macro, RegFormat, appears in the list of macros, but it won't do anything. Allow me to describe the steps I've taken, and maybe you can help me sort this out.

    I opened the VBE and clicked Insert > Module, then keyed in your code. I saved my work, then clicked File > Close and Return to Microsoft Excel. Back in Excel, I click Tools > Macro > Macros... and then Run. I feel like Chevy Chase in Christmas Vacation trying to get his holiday lights to work. I have security set to Low, thinking maybe my security settings were too high. Any suggestions?

    I'm going to try RuthCallcott's idea, but I really would like to understand why my macro is a dud. Thanks!

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clean up data (2003)

    Set security to medium.
    Then close and reopen the workbook and make sure you enable macros.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clean up data (2003)

    First, set your security to medium. It is not a good idea to run with security set to low, nasty things can happen when on the web. However, that should not keep the macro from running. Was the sheet that contained the glossary the active sheet when you ran the macro. My macro code works on the active sheet since you did not specify any way of identifying the sheet that contains the glossary data. If this does not help, could you upload the workbook so we can take a look? Change any proprietary data to make it useless.
    Legare Coleman

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Clean up data (2003)

    Thanks, Legare: I've reset the security, closed and re-opened the file and tried running the macro and...nada.

    I'm posting the file

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clean up data (2003)

    There are a number of problems. The reason that the code did not do anything is that there is a typo. You apparently retyped the code rather than copy it from my message and paste it into your Excel. If you want to copy code from the lounge and paste it into a workbook, there is a procedure for doing this:

    1- Copy the code from the post in the lounge.

    2- Open WordPad and paste the code into it.

    3- Copy the code from WordPad and then close WordPad (you don't need to save the changes).

    4- Go to Excel and paste the code into the VBE editor.

    This procedure will paste the code properly into VBE.

    Anyhow, when you typed the code, the line which reads:

    <pre> For I = lLastRow To 1 Step -3
    </pre>


    was typed as:

    <pre> For I = LastRow To 1 Step -3
    </pre>


    The variable lLastrow was missing the first small letter l. That caused the code to do nothing, exactly as you described.

    However, after fixing that problem, the code still did not work. The reason was that the format of your worksheet is not consistent. There are a number of places where there is no blank row between glossary entries. For example, there is now blank row between rows 1007 and 1008. That threw the macro off so that it was working with the wrong rows and therefore not doing what it should be doing. There are also a number of places where there are what looks like title rows to identify where the section of the glossary came from. For example, row 838. In these cases, there was one blank row after it, but no definition so there was only two rows instead of three and that threw the macro off.

    The attached file has the corrected macro in it, and I have inserted blank rows in the worksheet where needed so that there are always groups of three rows as you originally described. The macro works on this worksheet.
    Legare Coleman

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Clean up data (2003)

    Legare, you've gone above and beyond! Thank you!

    You are correct: I did type in your code. I'd tried copying and pasting code directly from a post into the VBE, with very unsatisfactory results. Thanks for the tip on how to copy and paste via WordPad.

    Lucas

Posting Permissions

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