Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Cleaning up a spreadsheet

  1. #1
    tonydepo is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    17

    Cleaning up a spreadsheet

    Can someone help me figure out how to clean up a spreadsheet when I import it to a table. The part no and subpart does not repeat during the original spreadsheet that I need to import, but I would like it to repeat when I import or run a query. I need the part no and subpart no to repeat. The following is the display I get from a spreadsheet



    Part no Sub part Description of issues
    A 123 Widget smeared paint
    Missing one componenet
    B 222 Widget off center
    Widget is not balance
    Want the results to look like this:
    Part no Sub part Description of issues
    A 123 Widget smeared paint
    A 123 Missing one componenet
    B 222 Widget off center
    B 222 Widget is not balance

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,834
    I think it is easier to clean it up on the spreadsheet in Excel first, before trying to import into Access (as record order really has no meaning in Access).
    It can be cleaned up pretty easily with Auto Fill features, or a short macro. Let us know if you need help with that.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    tonydepo is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    17
    Thanks for the reply. I would like help with that since I am a novice. Thanks

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,834
    OK, here is an easy way to do it in Excel.

    Let's say that your first blank in column A is in row 3. So the value in A3 should be the same as the value in cell A2.
    In cell A3, enter this formula: =A2
    Now, highlight cell A3 and press copy.
    Then select all the cells in columns A and B starting with your first blank you need fixed, and ending with the last blank you need fixed (don't worry that there are values in that range that do not need to be fixed).
    Press F5, click "Special" and then select the "Blanks" radio button and click OK.
    Hit enter.

    This should populate all your blanks with formulas copying the value from the cell above them.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  5. #5
    tonydepo is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    17
    Thanks.. Is there a way to automate this going forward. I am currently downloading the file from a report interface that dumps into excel. Also, the number of issues can vary too from time to time.

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,834
    Yes, you could create an Excel macro that loops through your list and fills in the missing data. So then you would just need to run that macro each time.
    I don't have time to write that for you at this moment, but might later on tonight. If you can just give me the "rules" (really, layout) of your Excel sheet, I can do that.
    More specifically:
    - What I am looking for is what columns do we need to apply this to (is it just A and B)?
    - What row does your data start row?
    - What column can I use to determine the last row of data (i.e. column C will always have data, so we can look at column C to dynamically find the last row of data)?
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  7. #7
    tonydepo is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    17
    Sent you a PM.

  8. #8
    tonydepo is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    17
    This thread has my same question. Going to even try this too.. http://p2p.wrox.com/access-vba/38681...ield-null.html

  9. #9
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,834
    You can upload files to this site. See here for how to do that: https://www.accessforums.net/faq.php...b3_attachments

    However, I should warn you that I cannot download files from my present location (corporate policy). However, I don't think it is really necessary anyhow, if you can answer those few questions I asked.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  10. #10
    tonydepo is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    17
    This seems to work, but only one record each time I run the update query, How do I get it to process all records for the update query?

    DLookUp("[Field2]","Tblnew","[ID] = " & [ID]-1)

  11. #11
    tonydepo is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    17
    Joe, to answer your questions:

    - What I am looking for is what columns do we need to apply this to (is it just A and B)? Needs to apply to column C and also Column F
    - What row does your data start row? 3
    - What column can I use to determine the last row of data (i.e. column C will always have data, so we can look at column C to dynamically find the last row of data)? D last record is with text "Agency Total: dynamicnumberhere"

    I would really like to see this completed as a one stop shop in access due to users.

  12. #12
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,834
    I had found a few minutes, and whipped up the Excel macro for you:
    Code:
    Sub MyAutoFill()
    
        Dim myLastRow
        Dim myRow As Long
        
        Application.ScreenUpdating = False
        
    '   Find last row with data in column D
        myLastRow = Cells(Rows.Count, "D").End(xlUp).Row
        
    '   Loop through all rows starting on row 3
        For myRow = 3 To myLastRow
            If Cells(myRow, "C") = "" Then Cells(myRow, "C") = Cells(myRow - 1, "C")
            If Cells(myRow, "F") = "" Then Cells(myRow, "F") = Cells(myRow - 1, "F")
        Next myRow
        
        Application.ScreenUpdating = True
        
    End Sub
    You can even call Excel macros from Access (see here: http://support.microsoft.com/kb/194611).
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  13. #13
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,834
    I would really like to see this completed as a one stop shop in access due to users.
    There are ways to do it in Access, though they are a little harder if you are not familiar with VBA and Recordsets. Personally, I don't think I would use the method described in the WROX link you posted (just my preference).

    I think I would import the data into an Access table, making sure that I have an Autonumber field in the table (this is important to maintain the records in the same order as they are in on the Excel file). Then I would create a VBA scripting using DAO Recordsets to loop through my data, row-by-row, and if a field is blank, populate it with the value from the previous record.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  14. #14
    tonydepo is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    17
    Quote Originally Posted by JoeM View Post
    I had found a few minutes, and whipped up the Excel macro for you:
    Code:
    Sub MyAutoFill()
    
        Dim myLastRow
        Dim myRow As Long
        
        Application.ScreenUpdating = False
        
    '   Find last row with data in column D
        myLastRow = Cells(Rows.Count, "D").End(xlUp).Row
        
    '   Loop through all rows starting on row 3
        For myRow = 3 To myLastRow
            If Cells(myRow, "C") = "" Then Cells(myRow, "C") = Cells(myRow - 1, "C")
            If Cells(myRow, "F") = "" Then Cells(myRow, "F") = Cells(myRow - 1, "F")
        Next myRow
        
        Application.ScreenUpdating = True
        
    End Sub
    You can even call Excel macros from Access (see here: http://support.microsoft.com/kb/194611).

    Joe, thanks for the time.. It works except for column F.. Column F does not start until row 4 , but Column C starts at row 3..

  15. #15
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,834
    I assume that if row 3 is really your first row of data for column C, it should always have data in it. So we probably really don't need to check that row.
    So try changing the loop to start on row 4, i.e.
    Code:
    For myRow = 4 To myLastRow
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. export to spreadsheet
    By slimjen in forum Import/Export Data
    Replies: 1
    Last Post: 07-25-2012, 04:15 PM
  2. Replies: 1
    Last Post: 03-05-2012, 04:21 PM
  3. Cleaning up the alphabet
    By ducecoop in forum Access
    Replies: 4
    Last Post: 10-28-2010, 08:33 AM
  4. Cleaning Data
    By Sck in forum Queries
    Replies: 1
    Last Post: 07-22-2010, 12:43 AM
  5. Cleaning Up Data - Need Help
    By NeedHelp in forum Access
    Replies: 2
    Last Post: 06-05-2010, 10:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums