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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,089
Messages
5,835,333
Members
430,351
Latest member
ddalton

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
Top