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

On Sep 10, 12:42*pm, Joel wrote:
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" wrote:
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?k...n58CLgVA&hl=en

Thank for any advise