How to sum cell values until it reaches a value then count the number of rows in a dynamic table

[sciphinupe]

Hello Everyone,

I am in a dilemma I hope some wise person can help solve. I have a dynamic table consisting of 150+ rows with 3 main categories I'm looking to extract information. I've tried to use all forms of nesting SUMIFS & COUNTIFS and arrays but I can't figure it out and haven't seen any video form Mr. Excel on this topic. I'm hoping someone can share a formula or information that will:

1. Sum the number of orders exclusively for each part but only UP TO THE AMOUNT OF STOCK ON HAND

2. Count the number of rows it took for the number of orders to be as close as or equal to the stock on hand

For example, in the chart below there are 13 rows of orders/customers for 2 separate part numbers. Part A has 15 orders however there are only 2 in stock, and part B has 470 orders for however we only have 39 in stock.

I would like a formula I can use (for all the part number in my table) to be able to look at the 6 rows of part A and add cells in increments until the order number doesn't surpass stock on hand (SOH) number; 2. Also, to do the same thing for the 7 rows for part B.

[table="width: 500, class: grid, align: left"]
[tr]
[td]Customers
[/td]
[td]PN[/td]
[td]Orders[/td]
[td]Stock on hand[/td]
[/tr]
[tr]
[td]1
[/td]
[td]Part A[/td]
[td]1[/td]
[td]2[/td]
[/tr]
[tr]
[td]2[/td]
[td]Part A[/td]
[td]3[/td]
[td]2[/td]
[/tr]
[tr]
[td]3[/td]
[td]Part A[/td]
[td]5[/td]
[td]2[/td]
[/tr]
[tr]
[td]4[/td]
[td]Part A[/td]
[td]2[/td]
[td]2[/td]
[/tr]
[tr]
[td]5[/td]
[td]Part A[/td]
[td]2[/td]
[td]2[/td]
[/tr]
[tr]
[td]6[/td]
[td]Part A[/td]
[td]2[/td]
[td]2[/td]
[/tr]
[tr]
[td]7[/td]
[td]Part B[/td]
[td]100[/td]
[td]39[/td]
[/tr]
[tr]
[td]8[/td]
[td]Part B[/td]
[td]100[/td]
[td]39[/td]
[/tr]
[tr]
[td]9[/td]
[td]Part B[/td]
[td]38[/td]
[td]39[/td]
[/tr]
[tr]
[td]10[/td]
[td]Part B[/td]
[td]100[/td]
[td]39[/td]
[/tr]
[tr]
[td]11[/td]
[td]Part B[/td]
[td]100[/td]
[td]39[/td]
[/tr]
[tr]
[td]12[/td]
[td]Part B[/td]
[td]1[/td]
[td]39[/td]
[/tr]
[tr]
[td]13[/td]
[td]Part B[/td]
[td]31[/td]
[td]39[/td]
[/tr]
[/table]



When calculating correctly I would like the formula to show for Part A one customer's order can be filled (because there is 2 SOH) but for Part B two customer's orders can be filled (customer 12 has 1 order & customer 13 has 31 orders; therefore, these two smaller orders within Part B's SOH should be satisfied first before that of customer 9's order).

I understand it's a lot to digest so hopefully someone will have an idea how I can tackle this issue. In summary, I need to understand how many customers' order lines have or have not been satisfied given the amount of available stock for each particular part. None the videos I watch for information I could find so far addresses how to count the number of remaining rows of a particular part after excluding rows that has met a criteria.

Thanks all so much!!!

Greg

 

[Partho]

Greg
The macro below will write "Order can be filled" in Column E (assuming columns A to D are populated as per your example data) in each of the order rows which satisfy the conditions.

Sub CheckOrders()
Dim StartRow As Long
Dim EndRow As Long
Dim SamePartRows As Long
Dim c As Range
Dim SumOrder As Long
Range("E:E").Clear
StartRow = 2
EndRow = StartRow + Application.WorksheetFunction.CountIf(Range("B:B"), Range("B" & StartRow).Value) - 1
'
Do Until EndRow = Range("B2").End(xlDown).Row Or EndRow > Range("B2").End(xlDown).Row
SamePartRows = Application.WorksheetFunction.CountIf(Range("B:B"), Range("B" & StartRow).Value)
EndRow = StartRow + SamePartRows - 1
Range(Range("B" & StartRow), Range("B" & EndRow)).Select
SumOrder = 0
For Each c In Selection
If c.Offset(0, 1).Value + SumOrder <= c.Offset(0, 2) Then
c.Offset(0, 3) = "Order can be filled"
SumOrder = SumOrder + c.Offset(0, 1).Value
End If
Next c
SamePartRows = Application.WorksheetFunction.CountIf(Range("B:B"), Range("B" & StartRow).Value)
StartRow = StartRow + SamePartRows
EndRow = StartRow + SamePartRows - 1
Loop
'
End Sub

Hope it helps!

 

[sciphinupe]

Hi Partho,

Thanks so much for your help! Two things I should have clarified:

1. For Part A there are only 2 items total and for Part B there are only 38 items total; the numbers shown in column D really is not per customer (the modified chart is below). Therefore, if 6 different customers are looking for Part A (total demand of 15) but we only have 2 parts available we'd like to satisfy as many customers as possible with the limited number of parts we have available.

[table="width: 500, class: grid, align: left"]
[tr]
[td]Customers
[/td]
[td]PN[/td]
[td]Orders[/td]
[td]Stock on hand[/td]
[/tr]
[tr]
[td]1
[/td]
[td]Part A[/td]
[td]1[/td]
[td]2[/td]
[/tr]
[tr]
[td]2[/td]
[td]Part A[/td]
[td]3[/td]
[td][/td]
[/tr]
[tr]
[td]3[/td]
[td]Part A[/td]
[td]5[/td]
[td][/td]
[/tr]
[tr]
[td]4[/td]
[td]Part A[/td]
[td]2[/td]
[td][/td]
[/tr]
[tr]
[td]5[/td]
[td]Part A[/td]
[td]2[/td]
[td][/td]
[/tr]
[tr]
[td]6[/td]
[td]Part A[/td]
[td]2[/td]
[td][/td]
[/tr]
[tr]
[td]7[/td]
[td]Part B[/td]
[td]100[/td]
[td]39[/td]
[/tr]
[tr]
[td]8[/td]
[td]Part B[/td]
[td]100[/td]
[td][/td]
[/tr]
[tr]
[td]9[/td]
[td]Part B[/td]
[td]38[/td]
[td][/td]
[/tr]
[tr]
[td]10[/td]
[td]Part B[/td]
[td]100[/td]
[td][/td]
[/tr]
[tr]
[td]11[/td]
[td]Part B[/td]
[td]100[/td]
[td][/td]
[/tr]
[tr]
[td]12[/td]
[td]Part B[/td]
[td]1[/td]
[td][/td]
[/tr]
[tr]
[td]13[/td]
[td]Part B[/td]
[td]31[/td]
[td][/td]
[/tr]
[/table]

2. There is another tab that is a pivot table using the data from this tab to sort out each part number, it's demand, and available stock on hand (SOH). I was hoping to avoid VBA (if possible) and find some COUNTIFS/SUMIFS & some other function as a nesting formula.


I've tried all combinations of COUNTIFS, SUMIFS, AGGREGATE, ROWS, and SUMPRODUCT however I still can not get the formula to only count the orders for the part I want (ignoring the other parts not selected), to sum only up to the number of SOH parts, then to count the number of rows it took to either match or not exceed the SOH number.

I don't know if anyone ever build a formula like this outside of VBA but if there some new function in Office Excel 2010 that I can use to count rows up to a certain number (while ignoring parts not in my criteria) that would be great!!

In the meantime I'll try your formula to see if I can make something happen.

Thanks again!!!

Greg

 

[Peter_SSs]

If you don't want vba then would some helper columns be acceptable?
If so, does this do what you want?

Each formula copied down.

Excel Workbook
	B	C	D	E	F	G
1	PN	Orders	Stock on hand	Rank	Can Fill	Total Can Fill
2	Part A	1	2	1	1
3	Part A	3	2	5
4	Part A	5	2	6
5	Part A	2	2	2
6	Part A	2	2	2
7	Part A	2	2	2		1
8	Part B	100	39	4
9	Part B	100	39	4
10	Part B	38	39	3
11	Part B	100	39	4
12	Part B	100	39	4
13	Part B	1	39	1	1
14	Part B	31	39	2	1	2
15	Part C	1	2	1	1
16	Part C	4	2	4
17	Part C	1	2	1	1
18	Part C	1	2	1		2
19
Orders

 

[sciphinupe]

Hi Peter,

Thanks so much! This is right in the ballpark of what I need however I have one more twist. Using your model how would I change the formula if I wanted to utilize as much stock on hand (SOH) to meet demand?

Let’s take Part B for example; what if I wanted to satisfy the customer in row 10 first then satisfy the customer in row 13? how would the formula change to "Rank" them 1 and 2 respectably instead of rows 13 and 14 as is currently shown on your table?

The rational is that by satisfying customers in row 10 [demand 38] and row 13 [demand 1] would not only be able to totally fulfill two customer orders but also ensure we have no remaining SOH. However, if I satisfied customers in rows 13 & 14 [demand of 1 and 31] I would be left with 7 parts left order (not enough to satisfy any of the other orders).

I'm trying to work with your formulas to see whether I can optimize the rankings but I'm so grateful you've been able to help me so much thus far!

If you have any additional ideas that can help I'd be ever so appreciative!

Regards,

Greg

 

[Peter_SSs]

This is right in the ballpark of what I need however I have one more twist. Using your model how would I change the formula if I wanted to utilize as much stock on hand (SOH) to meet demand?

Let’s take Part B for example; what if I wanted to satisfy the customer in row 10 first then satisfy the customer in row 13? how would the formula change to "Rank" them 1 and 2 respectably instead of rows 13 and 14 as is currently shown on your table?

The rational is that by satisfying customers in row 10 [demand 38] and row 13 [demand 1] would not only be able to totally fulfill two customer orders but also ensure we have no remaining SOH. However, if I satisfied customers in rows 13 & 14 [demand of 1 and 31] I would be left with 7 parts left order (not enough to satisfy any of the other orders).

I did wonder about that myself when I read your original request, but didn't ask about it because you specifically stated:

.. for Part B two customer's orders can be filled (customer 12 has 1 order & customer 13 has 31 orders; therefore, these two smaller orders within Part B's SOH should be satisfied first before that of customer 9's order).

Anyway, I'll have a look and see.

 

[Peter_SSs]

I did wonder about that myself when I read your original request, but didn't ask about it because you specifically stated:
Anyway, I'll have a look and see.

I don't see any feasible way to test all combinations to determine which combination comes closest to using the whole stock, particularly with a formula.

vba may be possible, but I'm not thinking of an easy way there either so far.

I don't know how big your actual lists are but even for the following example, I don't see a feasible logical way to arrive at the best total <= 103. For example, I can't see that ranking the values would be any use at all.
There would be the added complication of what to do if two (or more) completely different combinations gave the same total 'best result', as in the following where that optimum total can be arrived at two ways.

Excel Workbook
	A	B	C
1	PN	Orders	Stock
2	Part A	17	103
3	Part A	91	103
4	Part A	13	103
5	Part A	32	103
6	Part A	18	103
7	Part A	16	103
8	Part A	5	103
9	Part A	21	103
10	Part A	5	103
Orders

 

[sciphinupe]

<<I don't know how big your actual lists are but even for the following example, I don't see a feasible logical way to arrive at the best total <= 103. For example, I can't see that ranking the values would be any use at all.
There would be the added complication of what to do if two (or more) completely different combinations gave the same total 'best result', as in the following where that optimum total can be arrived at two ways.

Orders

*	A	B	C
PN
Part A
Part A
Part A
Part A
Part A
Part A
Part A
Part A
Part A

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

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

[TD="bgcolor: #cacaca, align: center"]2[/TD]

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

[TD="bgcolor: #cacaca, align: center"]3[/TD]

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

[TD="bgcolor: #cacaca, align: center"]4[/TD]

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

[TD="bgcolor: #cacaca, align: center"]5[/TD]

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

[TD="bgcolor: #cacaca, align: center"]6[/TD]

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

[TD="bgcolor: #cacaca, align: center"]7[/TD]

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

[TD="bgcolor: #cacaca, align: center"]8[/TD]

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

[TD="bgcolor: #cacaca, align: center"]9[/TD]

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

[TD="bgcolor: #cacaca, align: center"]10[/TD]

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

</tbody>

>>


Thanks Peter. I've been trying to find a way to get this last step also but I think your original formula is the best work-around answer. Your chart above does pose an interesting paradox that can't easily be solved in Excel.

Using your original formula I believe will accomplish 95% of what needs to be done, and if by some chance there is a case where one set of orders is a better selection than another set of orders the overall difference/impact I bet will be nominal.

I'll keep thinking about how to optimize this last step but in the meanwhile I have to thank you and Partho so much again for working on the original formula and sharing your findings with me. I will put them to good use!

Regards,

Greg

 

[sciphinupe]

Afternoon Peter,

I found a glitch with the formula. If the number 1 is used for stock on hand the formula won't put 1 as the "can fill" value (especially for multiple customer orders of 1). See the table below:

*	PN	Orders	Stock on hand
PN
Part A
Part A
Part A
Part A

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

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

[TD="bgcolor: #cacaca, align: center"]2[/TD]

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

[TD="bgcolor: #cacaca, align: center"]3[/TD]

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

[TD="bgcolor: #cacaca, align: center"]4[/TD]

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

[TD="bgcolor: #cacaca, align: center"]5[/TD]

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

</tbody>

>>

I'm trying to figure how to adjust your formula to work with 1s in the SOH cells. If you have any ideas that I can try I'd appreciate it.

Thanks again!

Greg

 

[Peter_SSs]

Afternoon Peter,

I found a glitch with the formula.

Not greatly tested, but does this fix it (& still work with the other circumstances)?

=IF(AND(SUMIFS(C$2:C$20,B$2:B$20,B2,E$2:E$20,"<"&E2)+C2<=D2,SUMIFS(C$2:C2,B$2:B2,B2,E$2:E2,"<="&E2)<=D2),1,"")