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