Results 1 to 11 of 11

Thread: [RESOLVED] Help Needed: Exported File needs too much clean up for a report

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    5

    Resolved [RESOLVED] Help Needed: Exported File needs too much clean up for a report

    Hi Guys,

    Just trying my luck here. I am having difficulties trying to clean up a data that I am exporting. Basically what I export somewhat of a CSV file for our attendance but how the data is arranged is like a time cardName:  time sheet.jpg
Views: 289
Size:  27.7 KB

    Now what I wanted is to have the Name and the ID number inline with the Date/Day/Schedule Setting Name:  time sheet 2.jpg
Views: 290
Size:  28.9 KB. Manually cleaning the data is too prone for error and too tedious. Is there a way for me to create a VB code so I can transpose those data into the same line?



    Thanks in advance.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Help Needed: Exported File needs too much clean up for a report

    Your question is about cleaning up data using code, not about creating reports, so it doesn't belong in the Reporting forum. It belongs in the appropriate language forum but we don't know what that is because you haven't specified whether you would be using VB6 or VB.NET.

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    5

    Re: Help Needed: Exported File needs too much clean up for a report

    Hi jmcilhinney,

    Thank you for the clarification, I am actually at lost on what to use. Basically what we use at the office is excel and as far as I know there is a VB inside excel. I don't know whether that is VB6 or VB.net.

    Thanks

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Help Needed: Exported File needs too much clean up for a report

    It's VBA (Visual Basic for Applications) that you use within an Office application, or you can use VSTO (Visual Studio Tools for Office) in Visual Studio. If that's the way that you want to go, there's a forum dedicated to Office Development. I've asked the mods to move this thread to there.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Help Needed: Exported File needs too much clean up for a report

    Welcome to VBForums

    I've moved this thread to the 'Office Development/VBA' forum.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Help Needed: Exported File needs too much clean up for a report

    it would be so nice if you had posted some data instead of images

    you can test like
    Code:
    Set src = Sheets("sheet2")
    Set targ = Sheets("sheet3")
    rw = 1
    For Each cel In src.Range("a:a")
        If IsEmpty(cel) Then Exit For
        If cel = "Name:" Then nm = cel.Offset(, 1): ID = cel.Offset(1, 1)
        If IsDate(cel) Then
            With targ.Cells(rw, 1)
                .Value = nm
                .Offset(, 1) = ID
                .Offset(, 2).Resize(, 4).Value = cel.Resize(, 4).Value
            End With
            rw = rw + 1
        End If
    Next
    i copied the data on to a blank worksheet, change sheet names to suit
    tested, runs without error, appears to give correct result
    Last edited by westconn1; Aug 18th, 2017 at 05:06 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Help Needed: Exported File needs too much clean up for a report

    Attached is a workbook with two tabs: 1) Raw data and 2) Cleaned data. I made a couple of assumptions. If you look at the raw data tab you'll see what I mean. I started the data in A1, and had each "section" the same number of days/rows. It was a bit unclear from your images if those are valid assumptions, so you may need to adjust the code accordingly.
    Attached Files Attached Files

  8. #8

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    5

    Re: Help Needed: Exported File needs too much clean up for a report

    Hi Guys,

    Thanks for all the replies and to the admins of the forum thank you for the clarification on where I should be posting my question. westconn1 and vbfbryce; I'll try both of your suggestions and comeback with the result. I don't know how I can attach a data file, when I tried the browser mentioned that the file is not a recognized file or something. By the way I was trying to attach a CSV file.

    Thanks again..

  9. #9

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    5

    Re: Help Needed: Exported File needs too much clean up for a report

    Hi again..

    I tried both codes but I guess I still really need to study VBA (for excel) from scratch. Using vbfbryce works like a charm from his attached file and data, but as soon as I replace the data with my own and run the macro, it just adds 2 blank columns on the worksheet. Anyways. Here's the complete data that I have.AttendanceReport(08012017-08202017).zip


    Thanks again in advanceAttendanceReport(08012017-08202017).zip

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Help Needed: Exported File needs too much clean up for a report

    i retested my code, with your uploaded data, it failed, as unlike your original sample, you had blank rows between each name

    i have fixed and added a blank row between each name in the target worksheet
    as your sample workbook had no other sheets i added a sheet for the new data

    Code:
    Set src = Sheets("detailed")
    Set targ = Sheets.Add
    rw = 1
    lrow = src.Cells(Rows.Count, 1).End(xlUp).Row
    For Each cel In src.Range("a1:a" & lrow)
    '    If IsEmpty(cel) Then Exit For
        If cel = "Name:" Then nm = cel.Offset(, 1): ID = cel.Offset(1, 1): rw = rw + 1
        If IsDate(cel) Then
            With targ.Cells(rw, 1)
                .Value = nm
                .Offset(, 1) = ID
                .Offset(, 2).Resize(, 4).Value = cel.Resize(, 4).Value
            End With
            rw = rw + 1
        End If
    Next
    i am not very explanatory with my code but it would be good if you can figure out what it does, feel free to ask if you need
    Last edited by westconn1; Aug 21st, 2017 at 04:24 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11

    Thread Starter
    New Member
    Join Date
    Aug 2017
    Posts
    5

    Re: Help Needed: Exported File needs too much clean up for a report

    Hi westconn1,

    Thanks for the code. I was able to make it work..as well as vbfbryce's. I'll make sure to ask questions regarding how these codes work and what they do.

    Thanks again and more power..

Posting Permissions

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



Click Here to Expand Forum to Full Width