Request for assistance

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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.
 
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
 
JW - Thanks!

Do I cut and paste this routine into a Macro window? Do I need to give it
the name "somethingelse"?

Thanks Again!
 
WOW - this worked JW! MANY THANKS!

JW said:
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
Click to expand...
 
Back
Top