Maximum value for repeated unique code

moryali · Mar 15, 2018

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

Area	Area No	Branch	Launch Date	Grade	Balance	Balance Should Be
South	52	Shams1	01-11-04	A	3,000	-
South	52	Shams3	01-10-04	A	12,000	10,000
South	52	Shams2	01-01-03	B	60,000	60,000
North	42	Badr1	01-01-05	A	6,000	6,000
North	42	Badr2	01-01-10	A	8,000	4,000
West	62	Fagr1	01-01-03	A	5,000	5,000
West	62	Fagr2	01-01-07	B	45,000	20,000
West	62	Fagr2	01-01-06	B	55,000	55,000
East	32	Badr1	01-01-02	B	50,000	50,000
East	32	Fagr1	01-01-02	B	50,000	30,000
East	32	Shams1	01-01-11	A	2,000	-

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

Eric W · Mar 15, 2018

Welcome to the forum.

Consider:

	A	B	C	D	E	F	G
1	Area	Area No	Branch	Launch Date	Grade	Balance	Balance Should Be
2	South	52	Shams1	1/11/2004	A	3,000	3000
3	South	52	Shams3	1/10/2004	A	12,000	12000
4	South	52	Shams2	1/1/2003	B	60,000	60000
5	North	42	Badr1	1/1/2005	A	6,000	6000
6	North	42	Badr2	1/1/2010	A	8,000	4000
7	West	62	Fagr1	1/1/2003	A	5,000	5000
8	West	62	Fagr2	1/1/2007	B	45,000	20000
9	West	62	Fagr2	1/1/2006	B	55,000	55000
10	East	32	Badr1	1/1/2002	B	50,000	50000
11	East	32	Fagr1	1/1/2002	B	50,000	30000
12	East	32	Shams1	1/1/2011	A	2,000	0

Sheet9

Worksheet Formulas

Cell	Formula
G2	=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)))

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.

moryali · Mar 15, 2018

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

moryali · Mar 15, 2018

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

A	B	C	D	E	F	G
1	Area	Area No	Branch	Launch Date	Grade	Balance	Balance Should Be
2	South	52	Shams1	1/11/2004	A	3,000	0
3	South	52	Shams3	1/10/2004	A	12,000	10000
4	South	52	Shams2	1/1/2003	B	60,000	60000

****** id="cke_pastebin" style="position: absolute; top: 8px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

A	B	C	D	E	F	G
1	Area	Area No	Branch	Launch Date	Grade	Balance	Balance Should Be
2	South	52	Shams1	1/11/2004	A	3,000	3000
3	South	52	Shams3	1/10/2004	A	12,000	12000
4	South	52	Shams2	1/1/2003	B	60,000	60000

</body>

Fluff · Mar 16, 2018

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.

Eric W · Mar 16, 2018

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.

moryali · Mar 17, 2018

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.

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

Eric W · Mar 19, 2018

Here's the formula in I2 with your additional requirement:

	A	B	C	D	E	F	G	I
1	Area	Area No	Branch	Launch Date	Grade	Balance	Balance Should Be
2	South	52	Shams1	1/11/2004	A	3,000	3000	0
3	South	52	Shams3	1/10/2004	A	12,000	12000	10000
4	South	52	Shams2	1/1/2003	B	60,000	60000	60000
5	North	42	Badr1	1/1/2005	A	6,000	6000	6000
6	North	42	Badr2	1/1/2010	A	8,000	4000	4000
7	West	62	Fagr1	1/1/2003	A	5,000	5000	5000
8	West	62	Fagr2	1/1/2007	B	45,000	20000	20000
9	West	62	Fagr2	1/1/2006	B	55,000	55000	55000
10	East	32	Badr1	1/1/2002	B	50,000	50000	50000
11	East	32	Fagr1	1/1/2002	B	50,000	30000	30000
12	East	32	Shams1	1/1/2011	A	2,000	0	0

Sheet1

Worksheet Formulas

Cell	Formula
G2	=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)))
I2	=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))))

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:

	A	B	C	D	E	F	G	I	K
1	Area	Area No	Branch	Launch Date	Grade	Balance	Balance Should Be
2	South	52	Shams1	1/11/2004 0:00	A	3,000	3000	0	0
3	South	52	Shams3	1/10/2004 0:00	A	12,000	12000	10000	10000
4	South	52	Shams2	1/1/2003 0:00	B	60,000	60000	60000	60000
5	North	42	Badr1	1/1/2005 0:00	A	6,000	6000	6000	6000
6	North	42	Badr2	1/1/2010 0:00	A	8,000	4000	4000	4000
7	West	62	Fagr1	1/1/2003 0:00	A	5,000	5000	5000	5000
8	West	62	Fagr2	1/1/2007 0:00	B	45,000	20000	20000	20000
9	West	62	Fagr2	1/1/2006 0:00	B	55,000	55000	55000	55000
10	East	32	Badr1	1/1/2002 2:00	B	50,000	29000	29000	29000
11	East	32	Fagr1	1/1/2002 1:00	B	51,000	51000	51000	51000
12	East	32	Shams1	1/1/2011 0:00	A	2,000	0	0	0

Sheet1

Worksheet Formulas

Cell	Formula
G2	=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)))
I2	=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))))
K2	=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)))

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.

moryali · Mar 26, 2018

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

Eric W · Mar 26, 2018

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.

Maximum value for repeated unique code

moryali

New Member

Excel Facts

Eric W

MrExcel MVP

moryali

New Member

moryali

New Member

Fluff

MrExcel MVP, Moderator

Eric W

MrExcel MVP

moryali

New Member

Eric W

MrExcel MVP

moryali

New Member

Eric W

MrExcel MVP

Forum statistics

Share this page

Maximum value for repeated unique code

New Member

Excel Facts

MrExcel MVP

New Member

New Member

MrExcel MVP, Moderator

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock