Dave,
I used codes in Access but never in excel. How do I make this work?
Please provide me with step by step. thank you.
By the way you're right 789 was not supposed by there, it was a typo.
Dave Peterson wrote:
I'm not sure how that 789 policy showed up in in LisaC's and Mac's data--I'm
guessing it was a typo in the post.
If that's true, then this seemed to work ok for me:
Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim oRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Set CurWks = Worksheets("Sheet1")
Set RptWks = Worksheets.Add
With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
oRow = -1
For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then
'same group, do nothing special
Else
'different group, do headers
oRow = oRow + 2
RptWks.Cells(oRow, "A").Value _
= "Owner: " & .Cells(iRow, "A").Value
oRow = oRow + 1
RptWks.Cells(oRow, "A").Value _
= "Beneficiary: " & .Cells(iRow, "A").Value
oRow = oRow + 2
RptWks.Cells(oRow, "A").Value = "Policy#"
RptWks.Cells(oRow, "B").Value = "Company"
End If
'do the policy stuff
oRow = oRow + 1
RptWks.Cells(oRow, "A").Value = "'" & .Cells(iRow, "C").Value
RptWks.Cells(oRow, "B").Value = "'" & .Cells(iRow, "D").Value
Next iRow
End With
End Sub
Personally, I'd use the data in the first sheet. I could apply
data|filter|autofilter and see exactly what I wanted. I could do sorts and
other stuff lots easier than the other version.
Hi everyone,
I tried lookup and pivot table/report but could not do what I am trying to
[quoted text clipped - 47 lines]
--
Message posted via http://www.officekb.com
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1