# Need to perform calculation within the same record

#### caprichoo

##### Board Regular
Dear All,

I have encounter some problem with the VBA coding. My situation is this, I have a set of data which consist of records of purchase. Let say customer A can have multiple entries in the database if he has more than one purchase. I am able to sort the record and group the duplicate records together. My problem is this There is a limit for which each customer can purchase so i need to know whether the purchase has exceed the limit.

For example customer A has 3 purchases and the limit is \$1000. For the 1st purchase is \$800, 2nd is \$150 and 3rd is \$300. So in the column beside these 3 entries, I need something like this. For the first entry show \$800 (since it is still within the allow limit) and for second entry show \$150(since \$800 + \$150 = \$950 and is still within the limit) and third entry show \$50 and will show the exceed amount of \$250 (since the 3rd purchase is \$300 and he only left with \$50 credit).

I would appreciate anyone who can help me solve this problem either by using excel formula or VBA. Thanks a millions in advance.

From Caprichoo

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Welcome to the board!

Try:

=MIN(MAX(1000-SUM(\$A\$1:A1),0),A2)

in B2 and dragged down.
Book1
ABCD
1
2800800
3150150
430050
51000
6
7
Sheet5

Hi ,

Thanks for the help. But the problem is what if I have more than one customer and each cuatomer can have 2,3 or 4 duplicates, then i won't be able to drag the formula down. Is it possible to use VBA?

Here formula is adjusted to coope with different customers:

=MIN(MAX(1000-SUMIF(\$A\$1:A1,A2,\$B\$1:B1),0),B2)
Book1
ABCD
1
2Customer1800800
3Customer2500500
4Customer3100100
5Customer1150150
6Customer2900500
7Customer3100100
8Customer130050
9Customer21000
10Customer3100100
11Customer11000
12Customer21000
13Customer3100100
14
Sheet5

Thanks fairwinds.... it helps....
Thanks a millions

Replies
1
Views
359
Replies
4
Views
203
Replies
2
Views
263
Replies
4
Views
343
Replies
6
Views
933

1,196,254
Messages
6,014,270
Members
441,810
Latest member
LouLou1234

### 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.

### Which adblocker are you using?

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

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