Sorting CSV Data By Date

[caveman.savant]

I have a text file with data that can look like:
04 IB12 75120 GREEN EA 5.00 5.00 2.00 14.00 0.00 6.86 12.
95
Aug ’08 3.0000 Nov ’07 1.0000 Feb ’07 0.0000 May ’06 3.0000 Yr 1 avg :
2.5833
Jul ’08 2.0000 Oct ’07 0.0000 Jan ’07 0.0000 Apr ’06 8.0000 total :
31.0000
Jun ’08 4.0000 Sep ’07 2.0000 Dec ’06 2.0000 Mar ’06 2.0000 Mn 1 stk :
1.94
May ’08 3.0000 Aug ’07 1.0000 Nov ’06 2.0000 Feb ’06 2.0000 Yr 2 avg :
2.3333
Apr ’08 4.0000 Jul ’07 1.0000 Oct ’06 1.0000 Jan ’06 3.0000 total :
28.0000
Mar ’08 4.0000 Jun ’07 5.0000 Sep ’06 3.0000 Dec ’05 2.0000 Mn 2 stk :
2.14
Feb ’08 6.0000 May ’07 2.0000 Aug ’06 1.0000 Nov ’05 3.0000 Yr 3 avg :
2.5833
Jan ’08 1.0000 Apr ’07 6.0000 Jul ’06 2.0000 2.0000 total : 31.0000
Dec ’07 1.0000 Mar ’07 5.0000 Jun ’06 3.0000 0.0000 Mn 3 stk : 1.94


04 IBG13 75121 GREEN EA 0.00 0.00 6.00 8.00 0.00 13.70 22.
95
Aug ’08 0.0000 Nov ’07 4.0000 Feb ’07 1.0000 May ’06 3.0000 Yr 1 avg :
1.7500
Jul ’08 1.0000 Oct ’07 2.0000 Jan ’07 2.0000 Apr ’06 4.0000 total :
21.0000
Jun ’08 0.0000 Sep ’07 2.0000 Dec ’06 2.0000 Mar ’06 3.0000 Mn 1 stk :
0.00
May ’08 1.0000 Aug ’07 1.0000 Nov ’06 3.0000 Feb ’06 0.0000 Yr 2 avg :
1.9167
Apr ’08 1.0000 Jul ’07 1.0000 Oct ’06 2.0000 Jan ’06 3.0000 total :
23.0000

This file can have dozens of this can of data sets. I want to break
down each set and copy it over to a new Excel sheet. The first 2 lines
would be combined and split where each space occurs.

The next part is much harder (I think). I want to take the next lines
and split and sort. Each Date is followed by a number for that month.
The last "Yr1 avg:..." and "total :..." are not needed. Once this is
done resort into Jan- Dec of each year.

Some of the lines do not show data for some months (see 2nd set- no
January thru March of 08 is seen). I want to show those months in the
resorted data as zero

I've been working on this awhile

An example spreadsheet of the result is at
http://spreadsheets.google.com/ccc?key=pgpAUsMDN5oVOoGn58CLgVA&hl=en

Thank for any advise

 

[Joel]

This took a little time. A challenge. The macto GetTextData() will prompt
for a filename and then open the file. I put the data in a worksheet called
unformated seperating the data by spaces. I then reformat the data by
putting it into a new sheet called master.

I then run a macro called BreakMaster. This macro first fills in the YR in
column A and calculates the total for each row. Then copies each set to its
own sheet.

Read the code and I will be glad to answer any questions. The code is a
little complicate to explain in writing. It is better that you look at the
code first. I believe there were some errors in you example. At the bottom
of this posting I also included my modified data.

Sub GetTextData()

FileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If FileToOpen = False Then
MsgBox ("Cannot Open file - Exiting Macro")
Exit Sub
End If

Set UnformatSht = Sheets.Add(after:=Sheets(Sheets.Count))
UnformatSht.Name = "Unformat Data"
Set MasterSht = Sheets.Add(after:=Sheets(Sheets.Count))
MasterSht.Name = "Master Data"


With UnformatSht.QueryTables.Add( _
Connection:="TEXT;" & FileToOpen, _
Destination:=UnformatSht.Range("A1"))

.Name = "monthly data"
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileSpaceDelimiter = True
.Refresh BackgroundQuery:=False
End With

MRowCount = 1
With UnformatSht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
ColA = .Range("A" & RowCount)
If ColA = "" Or IsNumeric(ColA) Then
If MasterSht.Range("C1") = "" Then
MRowCount = 1
Else
MRowCount = MasterSht.Range("C" & Rows.Count).End(xlUp).Row
MRowCount = MRowCount + 1
End If
.Rows(RowCount).Copy _
Destination:=MasterSht.Rows(MRowCount)
MRowCount = MRowCount + 1
Else

ColCount = 1
Do While (1)
data = .Cells(RowCount, ColCount)
If data = "Yr" Or _
data = "total" Or _
data = "Mn" Then

Exit Do
Else
MyYear = .Cells(RowCount, (ColCount + 1))
'remove single quote from year
MyYear = Val(Mid(MyYear, 2)) + 2000
YearRow = (Year(Date) - MyYear) + MRowCount


With MasterSht
If .Range("C" & YearRow) = "" Then

'fill in dates and amount = 0
For MasterMonth = 1 To 12
MonthCol = (2 * (MasterMonth - 1) + 3)

MyDate = MasterMonth & "/1/" & MyYear
MasterSht. _
Cells(YearRow, MonthCol) = MyDate
MasterSht.Cells(YearRow, MonthCol). _
NumberFormat = "mm/dd/yyyy"
MasterSht. _
Cells(YearRow, MonthCol + 1) = 0
Next MasterMonth
End If
End With

Amount = .Cells(RowCount, (ColCount + 2))
MyMonth = .Cells(RowCount, ColCount)
MyDate = MyMonth & " 1, " & MyYear
MonthCol = (2 * (Month(MyDate) - 1) + 3)

MasterSht.Cells(YearRow, MonthCol + 1) = _
Amount
End If
ColCount = ColCount + 3
Loop
End If
Next RowCount
End With


End Sub


Sub BreakMaster()


With Sheets("Master Data")

RowCount = 1
LastRow = .Range("C" & Rows.Count).End(xlUp).Row

Do While RowCount <= LastRow
If .Range("A" & RowCount) <> "" Then
FirstRow = RowCount
SetName = .Range("B" & FirstRow)
Set NewSht = Sheets.Add( _
after:=Sheets(Sheets.Count))
NewSht.Name = SetName
End If

If .Range("A" & (RowCount + 1)) <> "" Or _
RowCount = LastRow Then

YearCount = 1
For DataRow = RowCount To (FirstRow + 1) Step -1
.Range("A" & DataRow) = "Yr" & YearCount

Total = 0
For MasterMonth = 1 To 12
MonthCol = (2 * (MasterMonth - 1) + 4)
Total = Total + .Cells(DataRow, MonthCol)
Next MasterMonth
.Range("B" & DataRow) = Total

YearCount = YearCount + 1
Next DataRow
.Rows(FirstRow & ":" & RowCount).Copy _
Destination:=NewSht.Rows(1)
NewSht.Columns.AutoFit
End If

RowCount = RowCount + 1
Loop
End With
End Sub

04 IB12 75120 GREEN EA 5.00 5.00 2.00 14.00 0.00 6.86 12.95
Aug ’08 3.0000 Nov ’07 1.0000 Feb ’07 0.0000 May ’06 3.0000 Yr 1 avg : 2.5833
Jul ’08 2.0000 Oct ’07 0.0000 Jan ’07 0.0000 Apr ’06 8.0000 total : 31.0000
Jun ’08 4.0000 Sep ’07 2.0000 Dec ’06 2.0000 Mar ’06 2.0000 Mn 1 stk : 1.94
May ’08 3.0000 Aug ’07 1.0000 Nov ’06 2.0000 Feb ’06 2.0000 Yr 2 avg : 2.3333
Apr ’08 4.0000 Jul ’07 1.0000 Oct ’06 1.0000 Jan ’06 3.0000 total : 28.0000
Mar ’08 4.0000 Jun ’07 5.0000 Sep ’06 3.0000 Dec ’05 2.0000 Mn 2 stk : 2.14
Feb ’08 6.0000 May ’07 2.0000 Aug ’06 1.0000 Nov ’05 3.0000 Yr 3 avg : 2.5833
Jan ’08 1.0000 Apr ’07 6.0000 Jul ’06 2.0000 total : 31.0000
Dec ’07 1.0000 Mar ’07 5.0000 Jun ’06 3.0000 Mn 3 stk : 1.94


04 IBG13 75121 GREEN EA 0.00 0.00 6.00 8.00 0.00 13.70 22.95
Aug ’08 0.0000 Nov ’07 4.0000 Feb ’07 1.0000 May ’06 3.0000 Yr 1 avg : 1.7500
Jul ’08 1.0000 Oct ’07 2.0000 Jan ’07 2.0000 Apr ’06 4.0000 total : 21.0000
Jun ’08 0.0000 Sep ’07 2.0000 Dec ’06 2.0000 Mar ’06 3.0000 Mn 1 stk : 0.00
May ’08 1.0000 Aug ’07 1.0000 Nov ’06 3.0000 Feb ’06 0.0000 Yr 2 avg : 1.9167
Apr ’08 1.0000 Jul ’07 1.0000 Oct ’06 2.0000 Jan ’06 3.0000 total : 23.0000

 

[caveman.savant]

Looks great!
I will add it to the code I was working

 

[Joel]

I think my code does everything you asked for. Let me know if there are any
problems.

 

[Joel]

I would recommend a change in your output so you don't take up so many
columns. I would add the year to column B and the months in the Column
Hearder so you results would look like this. Most people do it like this.

04 IBG13 75121 GREEN EA 0.00 0.00 6.00 8.00 0.00 13.70 22.95
Total Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Yr4 2008 31 1 2 3 0 0 0 2 1
Yr3 2007 31 1 2 3 0 0 0 2 1

 

[caveman.savant]

The code for BreakMaster sends the debugger to the EndIf of the 1st
If Statement

 

[caveman.savant]

I put the raw data at
http://sites.google.com/site/cavemansavant/Home/raw-data

BTW my raw data wraps the last two digits of the 1st line to a second
line, but should be cannotated to the 1st line
so that
04 IBG10 BOOTS IRRIGATION 75120 GREEN SIZE EA -5.00 -5.00 2.00 14.00
0.00 13.70 22.
95

is
04 IBG10 BOOTS IRRIGATION 75120 GREEN SIZE EA -5.00 -5.00 2.00 14.00
0.00 13.70 22.95

 

[caveman.savant]

The code for BreakMaster sends the debugger to the EndIf of the 1st
If Statement


I put the raw data at
http://sites.google.com/site/cavemansavant/Home/raw-data

BTW my raw data wraps the last two digits of the 1st line to a second
line, but should be cannotated to the 1st line
so that
04 IBG10 BOOTS IRRIGATION 75120 GREEN SIZE EA -5.00 -5.00 2.00 14.00
0.00 13.70 22.
95

is
04 IBG10 BOOTS IRRIGATION 75120 GREEN SIZE EA -5.00 -5.00 2.00 14.00
0.00 13.70 22.95

 

[Joel]

Do the first macro work. I assume it does becasue you are getting to the 2nd
macro. The worksheet "Master Data should be completely filled in except for
some data in columns A & B. The second macro fillin the rest of columns A &
b and then seperates this sheet into multiple sheets.

I think you ran the macro more than once or the header Name BG13 (or
equivalent) exists in more than one header. I'm using the the name in Column
B of the header row as the sheet name. If this is the problem you should see
some of the split worksheets exist and filled in with data. You would also
see one new worksheet called SheetXX with no data.

I can also see an error occuring if the worksheet already existed if you ran
the code twice. When you run the First Macro GetTextData() the sheet "Master
Data" sheet cannot exist otherwise you will get an error. I did not include
code to test if the sheet already existed I just created the new sheet
"Master Data". The same problem would occur if you ran the 2nd macro more
than once.

If I din't find the problem let me know which worksheets were created and
how much data is one these sheets. If you do have duplicate names in the
header row then I need to modify the code to come up with a different naming
convention for the sheets. I coiuld check for duplicates an make the sheet
names BB13(2), BG13(3) to handle the duplicate names.

A quick fix would be to comment out the line below by putting a single quote
in front of this line. This will not rename the worksheets.

'NewSht.Name = SetName