I figured it out and it seems to be working. Thank you! Here is the macro I
used...
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:=xlPart, _
SearchDirection:=xlNext)
If Not One Is Nothing Then
FirstAddressRow = One.Row
Set Twelve = Rng.Find("3", After:=One, LookIn:=xlValues, _
LookAt:=xlPart, SearchDirection:=xlNext)
Do
With Range(One.Offset(1), Twelve.Offset(-1)).Resize(, 10)
.Copy One.Offset(1, 9)
.Resize(, .Columns.Count - 1).Value = One.Resize(, 9).Value
One.EntireRow.Delete
End With
Set One = Rng.Find("1", After:=Twelve, LookIn:=xlValues, _
LookAt:=xlPart, SearchDirection:=xlNext)
Set Twelve = Rng.Find("3", After:=One, LookIn:=xlValues, _
LookAt:=xlPart, SearchDirection:=xlNext)
Loop While One.Row > FirstAddressRow
End If
End With
End Sub
"krisfj40" wrote:
> Rick,
> Thank you! This is "almost" working. The reason I say almost is because my
> header record 1 has data in columns A, B, C, D, G, H, and I (notice E, F are
> blanks). The data in record type 2 has data in columns A-H, J for every row
> and some of them also use column I (but not all).
>
> The macro is only grabbing the header record data in columns A, B, C, and D.
>
> As you have probably noticed, I am not a programmer, so your assistance is
> greatly appreciated!!
>
> Kris
> "Rick Rothstein" wrote:
>
> > 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" <(E-Mail Removed)> wrote in message
> > news:CB5B356E-E6A3-4792-8433-(E-Mail Removed)...
> > >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!
> >
> > .
> >
Similar Threads
