Maximum value for repeated unique code

M

moryali

New Member
Joined
Mar 15, 2018
Messages
12
Good Day
I have clients with unique number and client are purchasing with credit balance so we have registry including all purchasing transactions over all the whole month and it is registered on rows
What I am seeking for is searching on the column related to transaction balance based on the unique number as I have maximum number per each client and purchasing transaction must not exceed this maximum per client not per transaction
[TABLE="width: 576"]
<tbody>[TR]
[TD]Area
[/TD]
[TD]Area No
[/TD]
[TD]Branch
[/TD]
[TD]Launch Date
[/TD]
[TD]Grade
[/TD]
[TD]Balance
[/TD]
[TD]Balance Should Be
[/TD]
[/TR]
[TR]
[TD]South
[/TD]
[TD]52
[/TD]
[TD]Shams1
[/TD]
[TD]01-11-04
[/TD]
[TD]A
[/TD]
[TD]3,000
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]South
[/TD]
[TD]52
[/TD]
[TD]Shams3
[/TD]
[TD]01-10-04
[/TD]
[TD]A
[/TD]
[TD]12,000
[/TD]
[TD]10,000
[/TD]
[/TR]
[TR]
[TD]South
[/TD]
[TD]52
[/TD]
[TD]Shams2
[/TD]
[TD]01-01-03
[/TD]
[TD]B
[/TD]
[TD]60,000
[/TD]
[TD]60,000
[/TD]
[/TR]
[TR]
[TD]North
[/TD]
[TD]42
[/TD]
[TD]Badr1
[/TD]
[TD]01-01-05
[/TD]
[TD]A
[/TD]
[TD]6,000
[/TD]
[TD]6,000
[/TD]
[/TR]
[TR]
[TD]North
[/TD]
[TD]42
[/TD]
[TD]Badr2
[/TD]
[TD]01-01-10
[/TD]
[TD]A
[/TD]
[TD]8,000
[/TD]
[TD]4,000
[/TD]
[/TR]
[TR]
[TD]West
[/TD]
[TD]62
[/TD]
[TD]Fagr1
[/TD]
[TD]01-01-03
[/TD]
[TD]A
[/TD]
[TD]5,000
[/TD]
[TD]5,000
[/TD]
[/TR]
[TR]
[TD]West
[/TD]
[TD]62
[/TD]
[TD]Fagr2
[/TD]
[TD]01-01-07
[/TD]
[TD]B
[/TD]
[TD]45,000
[/TD]
[TD]20,000
[/TD]
[/TR]
[TR]
[TD]West
[/TD]
[TD]62
[/TD]
[TD]Fagr2
[/TD]
[TD]01-01-06
[/TD]
[TD]B
[/TD]
[TD]55,000
[/TD]
[TD]55,000
[/TD]
[/TR]
[TR]
[TD]East
[/TD]
[TD]32
[/TD]
[TD]Badr1
[/TD]
[TD]01-01-02
[/TD]
[TD]B
[/TD]
[TD]50,000
[/TD]
[TD]50,000
[/TD]
[/TR]
[TR]
[TD]East
[/TD]
[TD]32
[/TD]
[TD]Fagr1
[/TD]
[TD]01-01-02
[/TD]
[TD]B
[/TD]
[TD]50,000
[/TD]
[TD]30,000
[/TD]
[/TR]
[TR]
[TD]East
[/TD]
[TD]32
[/TD]
[TD]Shams1
[/TD]
[TD]01-01-11
[/TD]
[TD]A
[/TD]
[TD]2,000
[/TD]
[TD]-
[/TD]
[/TR]
</tbody>[/TABLE]

Unique number will be based on area number
Priority classification will be based on

  • Oldest dates for launch dates then new dates
  • Maximum per same area with same grade A is 10,000 and same area with same grade B 80,000
  • If area has the 2 grades A & B so will be 80000 but if area has more than one record under A so A records maximum is 10,000
  • If area has same launching date so highest balance will be first priority with the respect of other above conditions

Example 1
Area ( South ) so excel formula will find area number 52 is repeated 3 times and area classified that has A & B so maximum area balance available up to 80,000 so first launch date is 01-01-03 so full balance then remaining will be 01-10-04 by 10,000 only not 12,000 as the maximum is 10,000 then remaining 01-11-04 will be zero as area already has another A

Example 2

  • Area ( North ) so excel formula will find area number 42 is repeated 2 times and area classified that has A so maximum area balance available up to 10,000 so first launch date is 01-01-05 so full balance 6,000 then remaining will be 01-01-10 by 4,000 only not 8,000 as the maximum is 10,000

Example 3

  • Area ( East ) so excel formula will find area number 32 is repeated 3 times and area classified that has A & B so maximum area balance available up to 80,000 so first launch date is 01-01-02 is repeated so full high balance 50,000 then remaining will be 01-01-02 by 30,000 only not 50,000 as the maximum is 80,000 then the remaining for date 01-01-11 will be zero as we already reached the maximum

I hope to find support to create this formula
Regards
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Welcome to the forum.

Consider:

ABCDEFG
AreaArea NoBranchLaunch DateGradeBalanceBalance Should Be
SouthShams1A
SouthShams3A
SouthShams2B
NorthBadr1A
NorthBadr2A
WestFagr1A
WestFagr2B
WestFagr2B
EastBadr1B
EastFagr1B
EastShams1A

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]52[/TD]

[TD="align: right"]1/11/2004[/TD]

[TD="align: right"]3,000[/TD]
[TD="align: right"]3000[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]52[/TD]

[TD="align: right"]1/10/2004[/TD]

[TD="align: right"]12,000[/TD]
[TD="align: right"]12000[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]52[/TD]

[TD="align: right"]1/1/2003[/TD]

[TD="align: right"]60,000[/TD]
[TD="align: right"]60000[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]42[/TD]

[TD="align: right"]1/1/2005[/TD]

[TD="align: right"]6,000[/TD]
[TD="align: right"]6000[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]42[/TD]

[TD="align: right"]1/1/2010[/TD]

[TD="align: right"]8,000[/TD]
[TD="align: right"]4000[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]62[/TD]

[TD="align: right"]1/1/2003[/TD]

[TD="align: right"]5,000[/TD]
[TD="align: right"]5000[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]62[/TD]

[TD="align: right"]1/1/2007[/TD]

[TD="align: right"]45,000[/TD]
[TD="align: right"]20000[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]62[/TD]

[TD="align: right"]1/1/2006[/TD]

[TD="align: right"]55,000[/TD]
[TD="align: right"]55000[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]32[/TD]

[TD="align: right"]1/1/2002[/TD]

[TD="align: right"]50,000[/TD]
[TD="align: right"]50000[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]32[/TD]

[TD="align: right"]1/1/2002[/TD]

[TD="align: right"]50,000[/TD]
[TD="align: right"]30000[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]32[/TD]

[TD="align: right"]1/1/2011[/TD]

[TD="align: right"]2,000[/TD]
[TD="align: right"]0[/TD]

</tbody>
Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=MEDIAN(0,F2,IF(COUNTIFS($A$2:$A$12,A2,$E$2:$E$12,"B"),80000,10000)-SUMPRODUCT($F$2:$F$12,--($A$2:$A$12=A2),--(INT($D$2:$D$12)+ROW($D$2:$D$12)/10000< D2+ROW(D2)/10000)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



If I understand your logic correctly, the amounts in G2:G3 are correct: 80,000 - 60,000 leaves 20,000 so G3 can have 12,000, leaving 8,000 for G2.

Also, if you have unique dates within an area, like if you could change the date in D11 to 1/2/2002 instead of the same date as D10, then you could use this simpler formula:

Code: 
=MEDIAN(0,F2,IF(COUNTIFS(A:A,A2,E:E,"B"),80000,10000)-SUMIFS(F:F,A:A,A2,D:D,"<"&D2))

Hope this works for you, let us know.
 
Last edited:
Upvote 0
First I would like to thank you for your fast reply and cooperation

But let me to explain more as you applied all conditions except one condition

Now you applied that the maximum if Area has A only is 10,000 and if Area has B only only is 80,000 and if Area has A & B you applied that maximum is 80,000 and it is correct except one another condition in case the Area has A & B the total must to not exceed 80,000 conditional that if the Area has more than one A inside we should applied A condition related 10,000 ( in our example here in G 2 should be Zero not 3,000 because the Area has another A took by 12,000 although the Area has remaining to reach 80,000 but it reached A level )

second if you can explain more for the code related to D10 because i can't catch your explanation noting that dates it is not unique but it can be equal that Area purchase different orders in same day so the priority order will be for the high amount , so if possible to explain more this code

Regards
 
Upvote 0
In addition to the second one transaction in G3 the maximum amount should be 10,000 not 12,000



[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Area[/TD]
[TD]Area No[/TD]
[TD]Branch[/TD]
[TD]Launch Date[/TD]
[TD]Grade[/TD]
[TD]Balance[/TD]
[TD]Balance Should Be[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]South[/TD]
[TD="align: right"]52[/TD]
[TD]Shams1[/TD]
[TD="align: right"]1/11/2004[/TD]
[TD]A[/TD]
[TD="align: right"]3,000[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]South[/TD]
[TD="align: right"]52[/TD]
[TD]Shams3[/TD]
[TD="align: right"]1/10/2004[/TD]
[TD]A[/TD]
[TD="align: right"]12,000[/TD]
[TD="align: right"]10000[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]South[/TD]
[TD="align: right"]52[/TD]
[TD]Shams2[/TD]
[TD="align: right"]1/1/2003[/TD]
[TD]B[/TD]
[TD="align: right"]60,000[/TD]
[TD="align: right"]60000[/TD]
[/TR]
</tbody>[/TABLE]
****** id="cke_pastebin" style="position: absolute; top: 8px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Area[/TD]
[TD]Area No[/TD]
[TD]Branch[/TD]
[TD]Launch Date[/TD]
[TD]Grade[/TD]
[TD]Balance[/TD]
[TD]Balance Should Be[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]South[/TD]
[TD="align: right"]52[/TD]
[TD]Shams1[/TD]
[TD="align: right"]1/11/2004[/TD]
[TD]A[/TD]
[TD="align: right"]3,000[/TD]
[TD="align: right"]3000[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]South[/TD]
[TD="align: right"]52[/TD]
[TD]Shams3[/TD]
[TD="align: right"]1/10/2004[/TD]
[TD]A[/TD]
[TD="align: right"]12,000[/TD]
[TD="align: right"]12000[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]South[/TD]
[TD="align: right"]52[/TD]
[TD]Shams2[/TD]
[TD="align: right"]1/1/2003[/TD]
[TD]B[/TD]
[TD="align: right"]60,000[/TD]
[TD="align: right"]60000[/TD]
[/TR]
</tbody>[/TABLE]
</body>
 
Upvote 0
Cross posted https://www.excelforum.com/search.php?searchid=7926584

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
So if you have a mixed area with at least 1 B and at least 2 As, the 80,000 applies only to the Bs, and the 10,000 applies only to the As?

Also, when there are more than 2 transactions on the same day, you want it prioritized by largest amount first? Right now the formula does it by whichever one shows up in the list first.

I think you might be asking too much of a formula, but I'll look at it.
 
Upvote 0
Eric W said:
So if you have a mixed area with at least 1 B and at least 2 As, the 80,000 applies only to the Bs, and the 10,000 applies only to the As?

Also, when there are more than 2 transactions on the same day, you want it prioritized by largest amount first? Right now the formula does it by whichever one shows up in the list first.

I think you might be asking too much of a formula, but I'll look at it.
Click to expand...

Thanks for your support and i will wait you if you reach any thinking toward the requested complex formula , in addition to if you don't mine to forward this subject to Access forum as the formula is very slow in implementation inside Excel for heavy records more than 200 K record
 
Upvote 0
Here's the formula in I2 with your additional requirement:

ABCDEFGHI
AreaArea NoBranchLaunch DateGradeBalanceBalance Should Be
SouthShams1A
SouthShams3A
SouthShams2B
NorthBadr1A
NorthBadr2A
WestFagr1A
WestFagr2B
WestFagr2B
EastBadr1B
EastFagr1B
EastShams1A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]52[/TD]

[TD="align: right"]1/11/2004[/TD]

[TD="align: right"]3,000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]52[/TD]

[TD="align: right"]1/10/2004[/TD]

[TD="align: right"]12,000[/TD]
[TD="align: right"]12000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10000[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]52[/TD]

[TD="align: right"]1/1/2003[/TD]

[TD="align: right"]60,000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]60000[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]42[/TD]

[TD="align: right"]1/1/2005[/TD]

[TD="align: right"]6,000[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6000[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]42[/TD]

[TD="align: right"]1/1/2010[/TD]

[TD="align: right"]8,000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4000[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]62[/TD]

[TD="align: right"]1/1/2003[/TD]

[TD="align: right"]5,000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5000[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]62[/TD]

[TD="align: right"]1/1/2007[/TD]

[TD="align: right"]45,000[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20000[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]62[/TD]

[TD="align: right"]1/1/2006[/TD]

[TD="align: right"]55,000[/TD]
[TD="align: right"]55000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]55000[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]32[/TD]

[TD="align: right"]1/1/2002[/TD]

[TD="align: right"]50,000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50000[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]32[/TD]

[TD="align: right"]1/1/2002[/TD]

[TD="align: right"]50,000[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30000[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]32[/TD]

[TD="align: right"]1/1/2011[/TD]

[TD="align: right"]2,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=MEDIAN(0,F2,IF(COUNTIFS($A$2:$A$12,A2,$E$2:$E$12,"B"),80000,10000)-SUMPRODUCT($F$2:$F$12,--($A$2:$A$12=A2),--($D$2:$D$12+ROW($D$2:$D$12)/10000< D2+ROW(D2)/10000)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=MEDIAN(0,F2,IF(COUNTIFS($A$2:$A$12,A2,$E$2:$E$12,"A")>1,IF(E2="A",10000,80000)-SUMPRODUCT($F$2:$F$12,--($A$2:$A$12=A2),--($E$2:$E$12=E2),--($D$2:$D$12+ROW($D$2:$D$12)/10000< D2+ROW(D2)/10000)),IF(COUNTIFS($A$2:$A$12,A2,$E$2:$E$12,"B"),80000,10000)-SUMPRODUCT($F$2:$F$12,--($A$2:$A$12=A2),--($D$2:$D$12+ROW($D$2:$D$12)/10000< D2+ROW(D2)/10000))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The required balances match your list. When you have ties on the date (D10:D11), the amount further up the sheet gets processed first, not the largest amount first. The formula still has the SUMPRODUCT function which causes it to be so slow. I think I could get it to process the amounts by size, but it would require changing the formula into an array formula, which would slow it down even more.

As one final alternative:

ABCDEFGHIJK
AreaArea NoBranchLaunch DateGradeBalanceBalance Should Be
SouthShams1A
SouthShams3A
SouthShams2B
NorthBadr1A
NorthBadr2A
WestFagr1A
WestFagr2B
WestFagr2B
EastBadr1B
EastFagr1B
EastShams1A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]52[/TD]

[TD="align: right"]1/11/2004 0:00[/TD]

[TD="align: right"]3,000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]52[/TD]

[TD="align: right"]1/10/2004 0:00[/TD]

[TD="align: right"]12,000[/TD]
[TD="align: right"]12000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10000[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]52[/TD]

[TD="align: right"]1/1/2003 0:00[/TD]

[TD="align: right"]60,000[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]60000[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]42[/TD]

[TD="align: right"]1/1/2005 0:00[/TD]

[TD="align: right"]6,000[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6000[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]42[/TD]

[TD="align: right"]1/1/2010 0:00[/TD]

[TD="align: right"]8,000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4000[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]62[/TD]

[TD="align: right"]1/1/2003 0:00[/TD]

[TD="align: right"]5,000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5000[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]62[/TD]

[TD="align: right"]1/1/2007 0:00[/TD]

[TD="align: right"]45,000[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20000[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]62[/TD]

[TD="align: right"]1/1/2006 0:00[/TD]

[TD="align: right"]55,000[/TD]
[TD="align: right"]55000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]55000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]55000[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]32[/TD]

[TD="align: right"]1/1/2002 2:00[/TD]

[TD="align: right"]50,000[/TD]
[TD="align: right"]29000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]29000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]29000[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]32[/TD]

[TD="align: right"]1/1/2002 1:00[/TD]

[TD="align: right"]51,000[/TD]
[TD="align: right"]51000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]51000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]51000[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]32[/TD]

[TD="align: right"]1/1/2011 0:00[/TD]

[TD="align: right"]2,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=MEDIAN(0,F2,IF(COUNTIFS($A$2:$A$12,A2,$E$2:$E$12,"B"),80000,10000)-SUMPRODUCT($F$2:$F$12,--($A$2:$A$12=A2),--($D$2:$D$12+ROW($D$2:$D$12)/10000< D2+ROW(D2)/10000)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=MEDIAN(0,F2,IF(COUNTIFS($A$2:$A$12,A2,$E$2:$E$12,"A")>1,IF(E2="A",10000,80000)-SUMPRODUCT($F$2:$F$12,--($A$2:$A$12=A2),--($E$2:$E$12=E2),--($D$2:$D$12+ROW($D$2:$D$12)/10000< D2+ROW(D2)/10000)),IF(COUNTIFS($A$2:$A$12,A2,$E$2:$E$12,"B"),80000,10000)-SUMPRODUCT($F$2:$F$12,--($A$2:$A$12=A2),--($D$2:$D$12+ROW($D$2:$D$12)/10000< D2+ROW(D2)/10000))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K2[/TH]
[TD="align: left"]=MEDIAN(0,F2,IF(COUNTIFS(A:A,A2,E:E,"A")>1,IF(E2="A",10000,80000)-SUMIFS(F:F,A:A,A2,E:E,E2,D:D,"<"&D2),IF(COUNTIFS(A:A,A2,E:E,"B"),80000,10000)-SUMIFS(F:F,A:A,A2,D:D,"<"&D2)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The SUMIFS should calculate much faster than a SUMPRODUCT, but you'll have to see on your own sheet. Notice the dates in column D. In order for the K2 formula to work, you need to break the tie when dates are the same within an area. To do so, you'll have to add a time to the date. As you can see, this also works for the I2 formula.
 
Upvote 0
Really thank you Eric Wfor your effort and support and you are right SUMIFS calculate much faster than a SUMPRODUCT but the only error for SUMIFS in case of there is 2 fields have the same launch date but no problem for it as i can catch it easily
 
Upvote 0
I'm glad it works for you. You might also consider an on-demand macro instead. Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. Paste the following code:

Code: 
Sub GetBalance()
Dim Results() As Variant, MyData As Variant, ctlBrk As String, Grades(1 To 2) As Long
Dim lr As Long, r As Long, r1 As Long, cbr As Long, ix As Long, MyFlag As Long, Tot1 As Double, Tot2 As Double

    Application.ScreenUpdating = False

' Add an extra column with ascending numbers so we can resort back to the original order
    Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    ReDim Results(1 To lr, 1 To 1)
    Range("H2") = 1
    Range("H3") = 2
    Range("H2:H3").AutoFill Destination:=Range("H2:H" & lr), Type:=xlFillDefault
    
' Now sort the sheet by Area/Launch Date/Balance
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("B2"), Order:=xlAscending
        .SortFields.Add Key:=Range("D2"), Order:=xlAscending
        .SortFields.Add Key:=Range("F2"), Order:=xlDescending
        .SetRange Range("A:H")
        .Header = xlYes
        .Apply
    End With
    
' Read the data
    MyData = Range("A1:H" & lr).Value
' Set the control break
    ctlBrk = MyData(2, 1)
    For r = 2 To lr
        Erase Grades
        ' Read until the next control break, count the number of As and Bs
        For r1 = r To lr
            If MyData(r1, 1) <> ctlBrk Then Exit For
            ix = IIf(MyData(r1, 5) = "A", 1, 2)
            Grades(ix) = Grades(ix) + 1
        Next r1
        cbr = r1 - 1
        ' Based on the number of As and Bs, decide what the maximum should be
        If Grades(1) > 1 And Grades(2) > 0 Then
            MyFlag = 2
            Tot1 = 10000
            Tot2 = 80000
        Else
            MyFlag = 1
            Tot1 = IIf(Grades(2) > 1, 80000, 10000)
        End If
        ' Now read through the Area, subtracting from the remaining balance as far as we can
        For r1 = r To cbr
            If MyFlag = 2 And MyData(r1, 5) = "B" Then
                Results(r1, 1) = IIf(Tot2 < MyData(r1, 6), Tot2, MyData(r1, 6))
                Tot2 = Tot2 - Results(r1, 1)
            Else
                Results(r1, 1) = IIf(Tot1 < MyData(r1, 6), Tot1, MyData(r1, 6))
                Tot1 = Tot1 - Results(r1, 1)
            End If
        Next r1
        ' Jump to the next area, resetting the control break
        r = cbr
        If r < lr Then ctlBrk = MyData(r + 1, 1)
    Next r


' Write out the results
    Results(1, 1) = "Should Be"
    Range("G1").Resize(lr) = Results
    
' Sort back to the original order, and delete the extra column
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("H2"), Order:=xlAscending
        .SetRange Range("A:H")
        .Header = xlYes
        .Apply
    End With
    Columns("H:H").Delete
    
    Application.ScreenUpdating = False
    
End Sub
Go back to the Excel window. Go to the sheet with the data on it. Press Alt-F8, choose GetBalance and click Run.

This assumes your data is in A:F, and the results will be put in G.
You don't have to worry about ties on the launch date, and the larger amounts are processed first.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

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