Aggregate duplicate entries in a long schedule

stpaul000

New Member
Joined
Apr 18, 2002
Messages
7
I've been using a Macro someone wrote in 123 some time back. I don't know how to translated into Excel vba. The Macro asks the user to input the following data: 1. starting row, 2. last row, and 3. The amount column number (to aggregate). Then it takes all the duplicates based on the first column (sorted accounts numbers) and the related amounts in these duplicated accounts are added. The ending result is a schedule with no duplicates and correct amounts per account.
I've use the subtotal tool, but it is not very flexible when you want to do further work with the schedule.

Can someone help me translate this into vba. This is the Lotus macro used:




STARTREC 5
ENDREC 1572
TACT 1433587980504
TAMT -2778
XCOUNT 2
TOTAL -7649
ROW 6
LC 1573
T 6

A {GETNUMBER "Enter the starting row no. ",STARTREC}~
{GETNUMBER "Enter the last row no. ",ENDREC}~
{GETNUMBER "Enter the amount column no. ",ROW}~
{LET T,0:VALUE}~
{GOTO}A1~
{DOWN STARTREC-1}~
{LET TACT,@CELLPOINTER("CONTENTS")}~
{RIGHT ROW-1}~
{LET TAMT,@CELLPOINTER("CONTENTS")}~
{LEFT ROW-1}~
{LET XCOUNT,1:VALUE}~
{LET TOTAL,TAMT:VALUE}~
{LET LC,STARTREC:VALUE}~
B {DOWN}~
{LET LC,@CELLPOINTER("ROW")}~
{IF LC>ENDREC}{SUB4}{QUIT}~
{IF TACT=@CELLPOINTER("CONTENTS")}{SUB1}{BRANCH B}~
{IF XCOUNT=1}{SUB2}{BRANCH B}~
{IF XCOUNT>1}{SUB3}{BRANCH B}~
{QUIT}~



SUB1 {RIGHT ROW-1}~
{LET TAMT,@CELLPOINTER("CONTENTS")}~
{LET TOTAL,TOTAL+TAMT}~
{LEFT ROW-1}~
{LET XCOUNT,XCOUNT+1}~
{LET TACT,@CELLPOINTER("CONTENTS")}~
{RETURN}~

SUB2 {RIGHT ROW-1}~
{LET TAMT,@CELLPOINTER("CONTENTS"):value}~
{LET TOTAL,TAMT:VALUE}~
{LEFT ROW-1}~
{LET TACT,@CELLPOINTER("CONTENTS")}~
{RETURN}~

RTN1 {RIGHT}

SUB3 /M
{FOR T,1,ROW-1,1,RTN1}
{DOWN}
{END}
{DOWN}~
{UP XCOUNT-1}~
{UP XCOUNT}~
{RIGHT ROW-1}~
/CC62276~~
{LEFT ROW-1}~
{LET ENDREC,ENDREC-XCOUNT+1}~
{DOWN}~
{RIGHT ROW-1}~
{LET TAMT,@CELLPOINTER("CONTENTS"):value}~
{LET TOTAL,TAMT:VALUE}~
{LEFT ROW-1}~
{LET TACT,@CELLPOINTER("CONTENTS"):value}~
{LET XCOUNT,1:VALUE}~
{RETURN}~



SUB4 {BEEP}~
{RETURN}~

Thanks in advance
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Just use Excel's Data | Consolidate... menu command to get results like those show in columns D:E below...
Book1
ABCDEFGH
1AccountAmountAmount
2ABC100ABC125
3XYZ250XYZ295
4ABC25KLM85
5KLM85
6XYZ45
7
8
Sheet1
 
Upvote 0

Forum statistics

Threads
1,219,096
Messages
6,146,276
Members
450,682
Latest member
augusto89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top