Solved: Macro to Re-arrange Data

[xlUser]

Hi,

I've just downloaded some data from a website and am completely frustrated by the inefficient way the site had recorded the information.

Currently the information is layed out by a block of rows for each data series (see attachment - current layout sheet) but I would like to consolidate this into a more efficient column format.

So for each series I currently I have the information layed out in blocks in the following format accross colums A, B and C:



Real-Time PeriodSeries ID:StartEndABCOMP2012-10-18CurrentTitle:Asset-backed Commercial Paper Outstanding2012-10-18CurrentSource:Board of Governors of the Federal Reserve System2012-10-18CurrentRelease:Commercial Paper Outstanding2012-10-18CurrentUnits:Billions of Dollars2012-10-18CurrentFrequency:Weekly, Ending Wednesday2012-10-18CurrentSeasonal Adjustment:Seasonally Adjusted2012-10-18CurrentNotes:For more information, please refer to2012-10-18Currenthttp://www.federalreserve.gov/releases/cp/about.htm


Instead I would like to display the data like this so that essentially each indviudal block is eliminated and data summaried in a more user freindly format. Current the last cell is row 30000!
Series ID:Title:Source:Release:Units:Frequency:Seasonal Adjustment:Notes:ABCOMPAsset-backed Commercial Paper OutstandingBoard of Governors of the Federal Reserve SystemCommercial Paper OutstandingBillions of DollarsWeekly, Ending WednesdaySeasonally AdjustedABS10YAsset-backed securities held by TALF LLC (Face value): Maturing inBoard of Governors of the Federal Reserve Systemetcetcetcetcetc


I'm not to bothered about including the real time/period start and period end columns in the summary or the notes feild - these would just be a nice to have. I just want to be able to filter or sort by the different feilds!

I'm thinking that to re-arrange this data it will be necessary to find every instance of series ID and then consolidate the data below it into a summary area and then do the next one but unfortunately not every block is the same length in terms of rows.

Hope someone can help. I'm sure this would be useful to others who use the federal reserve site!

[Bob Phillips]

Sub ProcessData()
Dim ws As Worksheet
Dim vecHeadings As Variant
Dim lastrow As Long
Dim nextrow As Long
Dim matchcol As Long
Dim i As Long

vecHeadings = Array("Series ID:", "Title:", "Source:", "Release:", _
"Units:", "Frequency:", "Seasonal Adjustment:")

Set ws = ActiveSheet
Worksheets.Add

With ActiveSheet

.Name = "Revised"
.Range("A1:G1").Value = vecHeadings
.Range("H1").Value = "Notes:"

lastrow = ws.Cells(.Rows.Count, "A").End(xlUp).Row
nextrow = 1
For i = 1 To lastrow

If ws.Cells(i, "A").Value <> "" Then

matchcol = 0
On Error Resume Next
matchcol = Application.Match(ws.Cells(i, "A").Value, vecHeadings, 0)
On Error GoTo 0
If matchcol > 0 Then

If ws.Cells(i, "A").Value = "Series ID:" Then nextrow = nextrow + 1

.Cells(nextrow, matchcol).Value = ws.Cells(i + 1, "A").Value
i = i + 1
End If
End If
Next i
End With

Set ws = Nothing
End Sub

[xlUser]

Thanks XLD.

Wow - this works perfectly. Great job - this will now actually make this data usable for me!

BTW - the Notes part - column H is empty, not really an issue for me as I wont be using it but thought I'd just highlight it as I know some of you guys are perfectionists!

Xluser

[Bob Phillips]

Did you want the Notes? I thought that you deliberately omitted it, and they do go multi-line.

[xlUser]

No XLD, don't worry about the notes. It's fine as it is.

Thanks.

XLS