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.



"ielmrani via OfficeKB.com" wrote:

Hi everyone,
I tried lookup and pivot table/report but could not do what I am trying to
accomplish.
I am able to do it in access by creating a report, but my boss would like it
to be done in excel

I generate an excel sheet like this:

Owner Beneficiary Policy# Company
JC MM 1234 A
JC MMC 1245 B
LisaC MA 654 A
LisaC MA 789 B
Mac BA 254 C
Mac BA 2541 C
Mac BA 2542 C


Is it possible to create group this info in another sheet like this:

Owner: JC
Beneficiary: MM

Policy# Company
1234 A

Owner: JC
Beneficiary: MMC

Policy# Company
1245 B

Owner: LisaC
Beneficiary: MA

Policy# Company
654 A
789 B

Owner: Mac
Beneficiary: BA

Policy# Company
789 B
254 C
2541 C
2542 C

Thank you.

--
Message posted via http://www.officekb.com

--

Dave Peterson