Need to perform calculation within the same record

caprichoo

Board Regular
Joined
Nov 2, 2005
Messages
53
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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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