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 --~--~---------~--~----~------------~-------~--~----~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit & Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~----------~----~----~----~------~----~------~--~---
