Maximum value for repeated unique code

[moryali]

Good Day

Thanks to note i have current open subject in Excel forum under the following link

https://www.mrexcel.com/forum/excel...m-value-repeated-unique-code.html#post5031325

I already reached partial results in the excel forum with full support with our colleagues their

so what is requested is to try to apply this formula on Access inside query because i tried to apply it on excel for file include more than 200 K records but i failed so i need to transfer the implementation on access in addtion to if we can complete what is missing in the formula inside ACCESS

[TABLE="class: cms_table, width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="class: cms_table, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="align: center"]Cell[/TH]
[TH="align: center"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] , align: center"]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]


Access File Link

https://ufile.io/mkaho

full details on the required formula discussion inside the Excel thread

Best Regards

 

[xenou]

I would suggest you complete the work in Excel first. It will be confusing to try to do this in two forums at the same time, and the Excel version is not being reported as complete yet. Also just to be clear there is no guarantee that putting this in Access will make it faster.

 

[moryali]

I would suggest you complete the work in Excel first. It will be confusing to try to do this in two forums at the same time, and the Excel version is not being reported as complete yet. Also just to be clear there is no guarantee that putting this in Access will make it faster.

Usual Access is faster than Excel with big volume of data especial excel take a lot of time in calculating formulas and my trial was just work by the current Formula i had it from Excel forum and complete the remaining manually till I reach new results because we may didn't reach any new idea so i didn't prefer to stop the work and to be in phases

Thanks for your cooperation and answer

 

[moryali]

Finally i reach solution for my excel file through Excel Forum

https://www.mrexcel.com/forum/excel...m-value-repeated-unique-code.html#post5037895


But due to data size more than 200 K per sheet so i would like to use and convert the attached file to access

https://ufile.io/1q39h

of course through Excel forum we reached solution through SUMIFS & SUMPRODUCT so i appreciate your support if you can help me to convert it to work through Access in case i have huge data size

 

[Micron]

If I may offer a word of advice - I think you are assuming anyone who knows a decent amount about Access would be able to derive a solution based on what you've posted. While some might, I'll bet there's many who cannot. I for one believe myself to be somewhat capable in Access, but have absolutely no clue as to what you're trying to accomplish, even after skimming over the solution in the other thread. That's mainly because the formulas and functions are too complex for me to bother trying to figure out. So I think you are limiting the responses you will get.

I would give it a few days to see if that is or is not the case. If you get no help, then provide sample data, the desired outcome, and as much about the process as you think necessary. Likely you will find that you don't provide enough info on your first attempt. Then you should have provided some starting info for members of both camps - Access and Excel.

 

[moryali]

Thank you Micron for your post and please let me attach to you file as you requested and i will explain to you the required process

Summary

1. I have area with unique code so i am depending on this unique code to filter if the area is repeated or not
2. If repeated i will check the grade filed as it is contain 2 grades as follow ( A & B ) so if
   1. Area grade is A so maximum balance should be not exceed 10,000 , what if area have 2 orders by grade A so total of both areas will be not exceed 10,000 that is means area have maximum cap by 10,000 whatever the number of orders and we depending on launching date ( come first , will be , out first in calculation )
   2. Area grade is B so maximum balance should be not exceed 80,000 , what if area have 2 orders by grade B so total of both areas will be not exceed 80,000 that is means area have maximum cap by 80,000 whatever the number of orders and we depending on launching date ( come first , will be , out first in calculation )
   3. Area grade is mixed A & B so maximum balance should be not exceed 80,000 conditional that first cap of A is applied , what if area have 2 orders by grade A & B so total of both areas will be not exceed 80,000 that is means area have maximum cap by 80,000 whatever the number of orders so A will be by maximum 10,000 and B will be by maximum 80,000 and total will be up to 80,000 we depending on launching date ( come first , will be , out first in calculation )

Example Area 52

South	52	Shams1	01-Nov-04	A	3000	0
South	52	Shams3	01-Oct-04	A	12000	10000
South	52	Shams2	01-Jan-03	B	60000	60000

<caption> Report </caption><thead>
[TH="bgcolor: #c0c0c0"] Area [/TH]
[TH="bgcolor: #c0c0c0"] Area No [/TH]
[TH="bgcolor: #c0c0c0"] Branch [/TH]
[TH="bgcolor: #c0c0c0"] Launch Date [/TH]
[TH="bgcolor: #c0c0c0"] Grade [/TH]
[TH="bgcolor: #c0c0c0"] Balance [/TH]
[TH="bgcolor: #c0c0c0"] New Balance [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>

Have 2 order under A and one order under B so total area will be 80,000 so we will start by oldest launching date it will be 01-Jan-03 and it is grade B and it is balance 60,000 so i will cover all balance 60,000 because it is not exceed 80,000 then i have remaining in cap 20,000 so i will go forward for next 01-Oct-04 and it is grade is A with balance 12,000 so i will take only 10,000 because the maximum cap for A is 10,000 third order is under grade A so it will be count and calculated by zero because i already consumed the maximum in A grade

Example Area 52

West	62	Fagr1	01-Jan-03	A	5000	5000
West	62	Fagr2	01-Jan-07	B	45000	20000
West	62	Fagr2	01-Jan-06	B	55000	55000

<caption> Report </caption><thead>
[TH="bgcolor: #c0c0c0"] Area [/TH]
[TH="bgcolor: #c0c0c0"] Area No [/TH]
[TH="bgcolor: #c0c0c0"] Branch [/TH]
[TH="bgcolor: #c0c0c0"] Launch Date [/TH]
[TH="bgcolor: #c0c0c0"] Grade [/TH]
[TH="bgcolor: #c0c0c0"] Balance [/TH]
[TH="bgcolor: #c0c0c0"] New Balance [/TH]

</thead><tbody>
</tbody><tfoot></tfoot>

Area have 3 order so i will take first launch date 01-Jan-03 and it is grade is A with balance 5,000 so i will take all balance because i didn't reach maximum cap of A grade
Then go for next launch date it will be 01-Jan-06 it is grade B with balance 55,000 so it will take all balance as i didn't reach maximum balance of grade B 80,000 so i will go for third order it is grade B with balance 45,000 so i will not take the whole balance because the remaining is only 20,000 because the total area is 80,000

Access File Link

https://ufile.io/g8fi6

Regards

 

[Micron]

Very difficult to follow the logic. Sorry, but I'm thinking this is a bit too involved for me. If I change my mind and manage to delve into it a bit more, I will post back if I have any questions or suggestions, but I'm not very hopeful at the moment.