Aggregate duplicate entries in a long schedule

[stpaul000]

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

 

[Mark W.]

Just use Excel's Data | Consolidate... menu command to get results like those show in columns D:E below...

Book1
	A	B	C	D	E	F	G	H
1	Account	Amount			Amount
2	ABC	100		ABC	125
3	XYZ	250		XYZ	295
4	ABC	25		KLM	85
5	KLM	85
6	XYZ	45
7
8
Sheet1