[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 card
Now what I wanted is to have the Name and the ID number inline with the Date/Day/Schedule Setting . 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?
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.
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.
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.
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
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.
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.
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
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
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.