| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Request for assistance
Hello...
Here is the way I have a spreadsheet formatted right now: LEASE ID RENT CODE CURRENT RENT ANNUAL RENT 00010011 BP1 2750.00 33000.00 00010011 BP2 2750.00 33000.00 73510701 BF1 1265.66 15187.92 73510701 BF2 1265.67 15188.04 73510701 BF3 1265.67 15188.04 00060011 BP1 7083.33 84999.96 00070011 BP1 6799.05 81588.60 00080011 BP1 15000.00 180000.00 00090011 BP1 4687.49 56249.88 Here is what I would LIKE it to be: LEASE ID RENT CODE CURRENT RENT ANNUAL RENT 00010011 BP1, BP2 5500.00 66000.00 73510701 BF1, BF2, BF3 3797.00 45564.00 00060011 BP1 7083.33 84999.96 00070011 BP1 6799.05 81588.60 00080011 BP1 15000.00 180000.00 00090011 BP1 4687.49 56249.88 What I need is for there to be a single record for each LEASE ID, with associated Rent Codes listed and Cuurent and Annual Rent amounts summed when there aer multiple LEASE ID's... Since there are a couple of thousand records in my file, any automated process would be GREATLY APPRECIATED! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Request for assistance
I would suggest using a Pivot Table, using the lease id and rent code as row
fields and monthly and annual rent as data items. -- Kevin Backmann "Lisa" wrote: Hello... Here is the way I have a spreadsheet formatted right now: LEASE ID RENT CODE CURRENT RENT ANNUAL RENT 00010011 BP1 2750.00 33000.00 00010011 BP2 2750.00 33000.00 73510701 BF1 1265.66 15187.92 73510701 BF2 1265.67 15188.04 73510701 BF3 1265.67 15188.04 00060011 BP1 7083.33 84999.96 00070011 BP1 6799.05 81588.60 00080011 BP1 15000.00 180000.00 00090011 BP1 4687.49 56249.88 Here is what I would LIKE it to be: LEASE ID RENT CODE CURRENT RENT ANNUAL RENT 00010011 BP1, BP2 5500.00 66000.00 73510701 BF1, BF2, BF3 3797.00 45564.00 00060011 BP1 7083.33 84999.96 00070011 BP1 6799.05 81588.60 00080011 BP1 15000.00 180000.00 00090011 BP1 4687.49 56249.88 What I need is for there to be a single record for each LEASE ID, with associated Rent Codes listed and Cuurent and Annual Rent amounts summed when there aer multiple LEASE ID's... Since there are a couple of thousand records in my file, any automated process would be GREATLY APPRECIATED! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Request for assistance
thanks, Kevin. That still leaves me with multiple rows for the lease id field.
"Kevin B" wrote: I would suggest using a Pivot Table, using the lease id and rent code as row fields and monthly and annual rent as data items. -- Kevin Backmann "Lisa" wrote: Hello... Here is the way I have a spreadsheet formatted right now: LEASE ID RENT CODE CURRENT RENT ANNUAL RENT 00010011 BP1 2750.00 33000.00 00010011 BP2 2750.00 33000.00 73510701 BF1 1265.66 15187.92 73510701 BF2 1265.67 15188.04 73510701 BF3 1265.67 15188.04 00060011 BP1 7083.33 84999.96 00070011 BP1 6799.05 81588.60 00080011 BP1 15000.00 180000.00 00090011 BP1 4687.49 56249.88 Here is what I would LIKE it to be: LEASE ID RENT CODE CURRENT RENT ANNUAL RENT 00010011 BP1, BP2 5500.00 66000.00 73510701 BF1, BF2, BF3 3797.00 45564.00 00060011 BP1 7083.33 84999.96 00070011 BP1 6799.05 81588.60 00080011 BP1 15000.00 180000.00 00090011 BP1 4687.49 56249.88 What I need is for there to be a single record for each LEASE ID, with associated Rent Codes listed and Cuurent and Annual Rent amounts summed when there aer multiple LEASE ID's... Since there are a couple of thousand records in my file, any automated process would be GREATLY APPRECIATED! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Request for assistance
Quick and dirty. Make sure you run this on test data before running
it on your main sheet. This is assuming your Lease IDs are in A, rect code in B, current rent in C, and annual rent in D. There is no error handling. Sub somethingelse() Dim i As Long i = 2 Do Until IsEmpty(Cells(i, 1)) With Cells(i, 1) If .Text = .Offset(1, 0).Text Then .Offset(0, 1).Value = _ .Offset(0, 1).Text & _ ", " & .Offset(1, 1).Text .Offset(0, 2).Value = _ .Offset(0, 2) + .Offset(1, 2) .Offset(0, 3).Value = _ .Offset(0, 3) + .Offset(1, 3) .Offset(1, 0).EntireRow.Delete Else i = i + 1 End If End With Loop End Sub Lisa wrote: thanks, Kevin. That still leaves me with multiple rows for the lease id field. "Kevin B" wrote: I would suggest using a Pivot Table, using the lease id and rent code as row fields and monthly and annual rent as data items. -- Kevin Backmann "Lisa" wrote: Hello... Here is the way I have a spreadsheet formatted right now: LEASE ID RENT CODE CURRENT RENT ANNUAL RENT 00010011 BP1 2750.00 33000.00 00010011 BP2 2750.00 33000.00 73510701 BF1 1265.66 15187.92 73510701 BF2 1265.67 15188.04 73510701 BF3 1265.67 15188.04 00060011 BP1 7083.33 84999.96 00070011 BP1 6799.05 81588.60 00080011 BP1 15000.00 180000.00 00090011 BP1 4687.49 56249.88 Here is what I would LIKE it to be: LEASE ID RENT CODE CURRENT RENT ANNUAL RENT 00010011 BP1, BP2 5500.00 66000.00 73510701 BF1, BF2, BF3 3797.00 45564.00 00060011 BP1 7083.33 84999.96 00070011 BP1 6799.05 81588.60 00080011 BP1 15000.00 180000.00 00090011 BP1 4687.49 56249.88 What I need is for there to be a single record for each LEASE ID, with associated Rent Codes listed and Cuurent and Annual Rent amounts summed when there aer multiple LEASE ID's... Since there are a couple of thousand records in my file, any automated process would be GREATLY APPRECIATED! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Request for assistance
JW - Thanks!
Do I cut and paste this routine into a Macro window? Do I need to give it the name "somethingelse"? Thanks Again! "JW" wrote: Quick and dirty. Make sure you run this on test data before running it on your main sheet. This is assuming your Lease IDs are in A, rect code in B, current rent in C, and annual rent in D. There is no error handling. Sub somethingelse() Dim i As Long i = 2 Do Until IsEmpty(Cells(i, 1)) With Cells(i, 1) If .Text = .Offset(1, 0).Text Then .Offset(0, 1).Value = _ .Offset(0, 1).Text & _ ", " & .Offset(1, 1).Text .Offset(0, 2).Value = _ .Offset(0, 2) + .Offset(1, 2) .Offset(0, 3).Value = _ .Offset(0, 3) + .Offset(1, 3) .Offset(1, 0).EntireRow.Delete Else i = i + 1 End If End With Loop End Sub Lisa wrote: thanks, Kevin. That still leaves me with multiple rows for the lease id field. "Kevin B" wrote: I would suggest using a Pivot Table, using the lease id and rent code as row fields and monthly and annual rent as data items. -- Kevin Backmann "Lisa" wrote: Hello... Here is the way I have a spreadsheet formatted right now: LEASE ID RENT CODE CURRENT RENT ANNUAL RENT 00010011 BP1 2750.00 33000.00 00010011 BP2 2750.00 33000.00 73510701 BF1 1265.66 15187.92 73510701 BF2 1265.67 15188.04 73510701 BF3 1265.67 15188.04 00060011 BP1 7083.33 84999.96 00070011 BP1 6799.05 81588.60 00080011 BP1 15000.00 180000.00 00090011 BP1 4687.49 56249.88 Here is what I would LIKE it to be: LEASE ID RENT CODE CURRENT RENT ANNUAL RENT 00010011 BP1, BP2 5500.00 66000.00 73510701 BF1, BF2, BF3 3797.00 45564.00 00060011 BP1 7083.33 84999.96 00070011 BP1 6799.05 81588.60 00080011 BP1 15000.00 180000.00 00090011 BP1 4687.49 56249.88 What I need is for there to be a single record for each LEASE ID, with associated Rent Codes listed and Cuurent and Annual Rent amounts summed when there aer multiple LEASE ID's... Since there are a couple of thousand records in my file, any automated process would be GREATLY APPRECIATED! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Request for assistance
WOW - this worked JW! MANY THANKS!
"JW" wrote: Quick and dirty. Make sure you run this on test data before running it on your main sheet. This is assuming your Lease IDs are in A, rect code in B, current rent in C, and annual rent in D. There is no error handling. Sub somethingelse() Dim i As Long i = 2 Do Until IsEmpty(Cells(i, 1)) With Cells(i, 1) If .Text = .Offset(1, 0).Text Then .Offset(0, 1).Value = _ .Offset(0, 1).Text & _ ", " & .Offset(1, 1).Text .Offset(0, 2).Value = _ .Offset(0, 2) + .Offset(1, 2) .Offset(0, 3).Value = _ .Offset(0, 3) + .Offset(1, 3) .Offset(1, 0).EntireRow.Delete Else i = i + 1 End If End With Loop End Sub Lisa wrote: thanks, Kevin. That still leaves me with multiple rows for the lease id field. "Kevin B" wrote: I would suggest using a Pivot Table, using the lease id and rent code as row fields and monthly and annual rent as data items. -- Kevin Backmann "Lisa" wrote: Hello... Here is the way I have a spreadsheet formatted right now: LEASE ID RENT CODE CURRENT RENT ANNUAL RENT 00010011 BP1 2750.00 33000.00 00010011 BP2 2750.00 33000.00 73510701 BF1 1265.66 15187.92 73510701 BF2 1265.67 15188.04 73510701 BF3 1265.67 15188.04 00060011 BP1 7083.33 84999.96 00070011 BP1 6799.05 81588.60 00080011 BP1 15000.00 180000.00 00090011 BP1 4687.49 56249.88 Here is what I would LIKE it to be: LEASE ID RENT CODE CURRENT RENT ANNUAL RENT 00010011 BP1, BP2 5500.00 66000.00 73510701 BF1, BF2, BF3 3797.00 45564.00 00060011 BP1 7083.33 84999.96 00070011 BP1 6799.05 81588.60 00080011 BP1 15000.00 180000.00 00090011 BP1 4687.49 56249.88 What I need is for there to be a single record for each LEASE ID, with associated Rent Codes listed and Cuurent and Annual Rent amounts summed when there aer multiple LEASE ID's... Since there are a couple of thousand records in my file, any automated process would be GREATLY APPRECIATED! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Working days and hours calculation - request assistance please! | Excel Worksheet Functions | |||
| Simplifying my Previous Request for Assistance | Links and Linking in Excel | |||
| Simplifying my previous Request for Assistance | Excel Worksheet Functions | |||
| Simplifying my previous Request for Assistance | Excel Discussion (Misc queries) | |||
| Simplifying my previous Request for Assistance | Links and Linking in Excel | |||
Linear Mode
