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

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

Thank you so much. I'll try it and I'll let you know.

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

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

Dave,
This is amazing. It works. What I did is go to sheet I right click it and
added the code. I open the code and click F5 to run it. can it be make as
macro I am not sure how to do that.
Thanks

ielmrani wrote:
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.

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.
[quoted text clipped - 56 lines]
--
Message posted via http://www.officekb.com

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

This actually belongs in a General module--not behind a worksheet.

You may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

"ielmrani via OfficeKB.com" wrote:

Dave,
This is amazing. It works. What I did is go to sheet I right click it and
added the code. I open the code and click F5 to run it. can it be make as
macro I am not sure how to do that.
Thanks

ielmrani wrote:
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.

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.
[quoted text clipped - 56 lines]
--
Message posted via http://www.officekb.com

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

--

Dave Peterson

Thanks Dave,
I only give you a sample of what I was trying to do. Now I am trying to
apply your code to a real sheet. I revised the code but it's not working.
Anytime I run the code it creates a new sheet, can it populate the data in an
existing sheet?
I tried to add new columns but they're not appearing in the sheet when I run
the code.

Please bear with me I am not an expert in excel, I am trying though. Thanks
so much.

Here is what I did so far:

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"
RptWks.Cells(oRow, "C").Value = "Effective Date"
RptWks.Cells(oRow, "D").Value = "Face Amount"
RptWks.Cells(oRow, "E").Value = "Cash Value"
RptWks.Cells(oRow, "C").Value = "Surrender Value"
RptWks.Cells(oRow, "C").Value = "Annual Premium"
RptWks.Cells(oRow, "C").Value = "Policy Type"

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
RptWks.Cells(oRow, "C").Value = "'" & .Cells(iRow, "E").Value
RptWks.Cells(oRow, "D").Value = "'" & .Cells(iRow, "F").Value
RptWks.Cells(oRow, "E").Value = "'" & .Cells(iRow, "G").Value
RptWks.Cells(oRow, "F").Value = "'" & .Cells(iRow, "H").Value
RptWks.Cells(oRow, "G").Value = "'" & .Cells(iRow, "I").Value
RptWks.Cells(oRow, "H").Value = "'" & .Cells(iRow, "J").Value
Next iRow
End With

End Sub



Dave Peterson wrote:
This actually belongs in a General module--not behind a worksheet.

You may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Dave,
This is amazing. It works. What I did is go to sheet I right click it and
[quoted text clipped - 16 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1