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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,650
Members
414,083
Latest member
Mrsash

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