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" wrote:
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