Give this macro a try (change the assignments in the two Const statements to
match your actual conditions)...

Sub ConsolidateDataRows()
Dim X As Long, FirstAddressRow As Long
Dim Rng As Range, One As Range, Three As Range
Const StartRow As Long = 1
Const SheetName As String = "Sheet1"
With Worksheets(SheetName)
Set Rng = .Range("A" & StartRow & ":A" & Rows.Count)
Set One = Rng.Find("1", After:=.Cells(Rows.Count, "A"), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchDirection:=xlNext)
If Not One Is Nothing Then
FirstAddressRow = One.Row
Set Three = Rng.Find("3", After:=One, LookIn:=xlValues, _
LookAt:=xlWhole, SearchDirection:=xlNext)
Do
With Range(One.Offset(1), Three.Offset(-1)).Resize(, 6)
.Copy One.Offset(1, 5)
.Resize(, .Columns.Count - 1).Value = One.Resize(, 5).Value
One.EntireRow.Delete
End With
Set One = Rng.Find("1", After:=Three, LookIn:=xlValues, _
LookAt:=xlWhole, SearchDirection:=xlNext)
Set Three = Rng.Find("3", After:=One, LookIn:=xlValues, _
LookAt:=xlWhole, SearchDirection:=xlNext)
Loop While One.Row FirstAddressRow
End If
End With
End Sub

--
Rick (MVP - Excel)


"krisfj40" wrote in message
...
I am working with a spreadsheet created from our mainframe and need some
assistance in converting it to a file easily used for data mining. The
records are numbered by 1,2,3 ; where 1=header row, 2=detailed records for
each header row, 3=sum count of the # of detailed records. Here is a
sample
of the data:

A B C D E F
1 Test Texas PlanNumber RedLight
2 John Addy State ZipCode Expense
2 Sally Addy State ZipCode Expense
2 Jake Addy State ZipCode Expense
2 Hank Addy State ZipCode Expense
3 4
1 Test Okl PlanNumber YellowLight
2 Lily Addy State ZipCode Expense
2 Deb Addy State ZipCode Expense
2 Joe Addy State ZipCode Expense
3 3

Here's the output I need:
A B C D E F G H
I J K
1 Test Texas PlanNumber RedLight 2 John Addy State ZipCode
Expense
1 Test Texas PlanNumber RedLight 2 Sally Addy State ZipCode
Expense
1 Test Texas PlanNumber RedLight 2 Jake Addy State ZipCode
Expense
1 Test Texas PlanNumber RedLight 2 Hank Addy State ZipCode
Expense
3 4

Any help would be GREATLY appreciated! I have 50 weekly files throughout
2009 with over 20k records each!