0

My spreadsheet contains in column A a list of items and in column B the quantity of each item:

Column A   Column B 
 110         30
 220         30
 380         60

I would like to display in a new cells the following logic:

If the minimal item value (110 for the start) is still available in quantity of 20, use minimal value.

If not then use then use the next minimal value on the list (also provided its quantity is more than 20) and so on.

As a result, it should show in a new array: 110 (10 times), 220 (10 times), 380 (40 times).

I used the formula below to identify the minimal value needed as a next entry

=VLOOKUP(MIN(A1:A3),A1:B3,2,0)

Lets say 110, 220 and 380 are items (voltage sockets, for example). The column B are the quantities. The stock has to issue the smallest voltage sockets first until its quantity reaches the minimal required quantity that must be always maintained in stock (20). Pulling C1 down, the order of issue of such sockets would be 10x110v (as only 10 can be issued out of 30), then 10x220V (as 220v qty on stock will also become 20) and then issue the 380v ones. what would be the ideal formula for C1 in order to pull that formula down the Column C and the sequence automatically to become 110, 110, 110, ... as it reaches 10 items and becomes 20 in stock, Cell C11 would show its time to start issuing 220v sockets, and cell C21 should show 380, as the previous sockets reach the issuing maximum quantities.

Any ideas, please?

Improve this question
  • I'm not following your description. It might help to see a mockup or screenshot. – fixer1234 Dec 9 '15 at 6:07
  • I cannot insert the screenshots right now, but idea is the new cell to choose the smallest amount in column A, find corresponding number in column B and insert one of its quantity numbers,provided the quantity value is still bigger than 20. when pull the cell down, it should do the same, dispersing "1" to each cell. these numbers automatically must be subtracted from the total, until it reaches 20. after, the next minimal value with the quantity of 20will be entered and so on. – SKYTTEN Dec 9 '15 at 9:40
  • I have edited the question. Sorry if it was confusing. – SKYTTEN Dec 10 '15 at 1:00
1

In below solution, the stock will be delivered in sequence of column A. So, if you want to deliver the lowest voltage first, please sort column A first.

  1. Enter into C1 0.

  2. Enter into C2 =IF(A1,MAX(B1-20,0)+C1,""), and copy downwards.

  3. Enter into D1 =IF(ROW(D1)<=MAX(C:C),INDEX(A:A,MATCH(ROW(D1)-1,C:C,1)),""), and copy downwards. Column D will be what you want.

Improve this answer
1

How many items do you have? As long as there is only a small number of items then the following might work:

In cell C1 type:

=IF(B1>20,A1,IF(B2>20,A2,IF(B3>20,A3,"No Stock")))

If there is less than 21 stock available of a particular item then the next item is checked for its stock number. If none have at least 20 available then it will provide the relevant text, or "" if you just want blank.

The more stock items, the longer the string, though.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.