Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting CSV Data By Date
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting CSV Data By Date
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting CSV Data By Date
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting CSV Data By Date
I think my code does everything you asked for. Let me know if there are any
problems. "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting CSV Data By Date
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting CSV Data By Date
The code for BreakMaster sends the debugger to the EndIf of the 1st
If Statement On Sep 11, 7:56*am, Joel wrote: I think my code does everything you asked for. *Let me know if there are any problems. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting CSV Data By Date
I put the raw data at
http://sites.google.com/site/caveman.../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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting CSV Data By Date
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/caveman.../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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting CSV Data By Date
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 "caveman.savant" wrote: 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/caveman.../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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data sorting according to the date | Excel Programming | |||
Sorting data by Date | Excel Worksheet Functions | |||
sorting data by date | Excel Worksheet Functions | |||
Sorting data by date | Excel Worksheet Functions | |||
Sorting a date range of data | Excel Programming |