decreasing from a stable Quantity Macro

[shahsavand]

i wanna decrease my stock with the last time i used my inventory.
for example ID no 1. my need is 200 and my stock is 5000 so my shortage is 5000-200=4800 then you can see we have this Id in another row in which my requirement is 54.here i wanna calculate it like this 4800-54=4746
(stock quantity is stable for each ID)
i need a macro for doing it.please somebody help me.

Product	ID	NEED	STOCK	Shortage
A	1	200	5000	4800
A	2	200	200	0
A	3	25	12	-13
A	4	5	36	31
A	5	6	254	248
B	6	4	23	19
B	7	21	36	15
B	1	54	5000	4946
B	2	6	200	194
c	65	69	100	31
c	3	54	12	-42
c	14	265	88	-177
c	26	32	99	67

<tbody>
</tbody>

 

[shahsavand]

Are you sure you are implementing it exactly as described? Here are my results:

Excel 2013

	A	B	C	D	E
1	Product	ID	NEED	STOCK	Shortage
2	A	1	200	5000	4800
3	A	2	200	200	0
4	A	3	25	12	-13
5	A	4	5	36	31
6	A	5	6	254	248
7	B	6	4	23	19
8	B	7	21	36	15
9	B	1	54	5000	4746
10	B	2	6	200	-6
11	c	1	69	100	4677
12	c	1	54	12	4623
13	c	1	265	5000	4358
14	c	1	32	5000	4326

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =IF( ISNUMBER(MATCH(B2, B$1:B1, 0)), LOOKUP(2, 1 / (B$1:B1 = B2), E$1:E1), D2) - C2 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hello again i wanted to expand your formula but i could not figure it out would you please help me?
i wanna calculate my shortage with considering the Replacement ID in other column as table below,
for example in column F i have an ID as replacement for ID 1 which is 12 and it has its own Stock and in column H i have ID 15 which is the second replace for my first ID and also has its own Stock.
i wanna use my first ID stock then use second and then use third one and where ever i face with each one of these three ID calculate from the last remained Stock

Product	ID	NEED	STOCK	Shortage	Replace one	Stock	Replace two	Stock
A	1	200	5000	4800	12	200	15	250
B	1	54	5000	4746	12	200	15	250
c	1	69	100	4677	12	200	15	250
c	1	54	12	4623	12	200	15	250
c	1	265	5000	4358	12	200	15	250
c	1	32	5000	4326	12	200	15	250
A	2	200	200	0	16	340
B	2	6	200	-6	16	340
A	3	25	12	-13	20	3500
C	20	215	3500	?	30	5000	40	10

<colgroup><col span="9"></colgroup><tbody>
</tbody>

 

[circledchicken]

Hi,

Can you please provide a more detailed example, showing expected outputs?
In the existing example its not completely clear what you need the results to look like?

 

[shahsavand]

Hi,

Can you please provide a more detailed example, showing expected outputs?
In the existing example its not completely clear what you need the results to look like?

for sure but its hard to explain and my english is not good enough i hope u can find out my purpose
i want to add the amount of first and second replacement to my first stock in order to calculate the whole possibility but the problem is our formula only calculates the stock of first cell from above and when you add some stock for example in cell D3 it doesn't count.

For example
Shortage for ID 1 in model B should be the last remained stock+last remained Stock of Replace One+last remained Stock of replace Two.

MODEL ID NEED STOCK Shortage Replace one Stock Replace two Stock TOTAl Stock A 1 200 5000 4800 12 200 15 250 5450 A 1 54 5000 4746 12 200 15 250 5450 A 1 69 5000 4677 12 200 13 500 5700 B 1 54 5000 4623 12 200 66 2666 7866 C 1 265 5000 4358 12 200 15 250 5450 D 1 32 5000 4326 12 200 15 250 5450 <colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[circledchicken]

Sorry, I'm not sure I follow this.

Can you apply the original formula replacing the STOCK column with the new Total Stock column?

If not, then perhaps you can provide a more detailed example with changing values in each of the columns and expected outputs and hopefully someone will be able to help.

 

[shahsavand]

Sorry, I'm not sure I follow this.

Can you apply the original formula replacing the STOCK column with the new Total Stock column?

If not, then perhaps you can provide a more detailed example with changing values in each of the columns and expected outputs and hopefully someone will be able to help.

total stock=column D+G+I
just consider what if your formula was base on column total stock.(means your stock is not stable anymore)
now how should i calculate the shortage

 

[circledchicken]

=IF(
    ISNUMBER(MATCH(B2, B$1:B1, 0)),
    LOOKUP(2, 1 / (B$1:B1 = B2), E$1:E1),
    J2)
  - C2

I'm not sure what you mean by 'your stock is not stable anymore'.

A more detailed example would help.

 

[shahsavand]

=IF(
    ISNUMBER(MATCH(B2, B$1:B1, 0)),
    LOOKUP(2, 1 / (B$1:B1 = B2), E$1:E1),
    J2)
  - C2

I'm not sure what you mean by 'your stock is not stable anymore'.

A more detailed example would help.

actually i want it this way:
when my shortage in under 0 it should use from stock of the replacements

 

[shahsavand]

=IF(
    ISNUMBER(MATCH(B2, B$1:B1, 0)),
    LOOKUP(2, 1 / (B$1:B1 = B2), E$1:E1),
    J2)
  - C2

I'm not sure what you mean by 'your stock is not stable anymore'.

A more detailed example would help.

would you please see whether it possible to lookup the smallest value if quantity of J2 is in A,D or G column exist.below table might help to understand .if the lookup criteria was not in Column A,D or G it should sum it up from column N
i insert my expected output

Code	quantity		Code	Quantity		Code	Quantity		Code	LookupValue			Total	quantity
1	100		6	500		12	100		1	must be= -1			1	10
2	200		7	600		13	120		10	must be= 290			2	20
3	230		1	20		15	130		16	must be = 3000			3	30
4	360		3	100		11	150						4	40
5	700		9	900		2	-22						5	50
1	50		10	290		3	-1						6	60
3	200		1	10		1	-20						7	70
													8	80
													9	90
													10	100
													11	110
													12	120
													13	130
													14	140
													15	150
													16	3000

<colgroup><col><col span="9"><col><col span="4"></colgroup><tbody>
</tbody>

 

[circledchicken]

Hi,

Your example isn't completely clear to me (e.g. I don't know why you have -1 as the LookupValue for the Code 1 - shouldn't it be -20?).
If that was just a typo, then perhaps try something like this:

=IFERROR(
    SMALL(
      CHOOSE(
        {1,2,3},
        IF(A$2:A$8 = J2, B$2:B$8),
        IF(D$2:D$8 = J2, E$2:E$8),
        IF(G$2:G$8 = J2, H$2:H$8)),
      1),
    LOOKUP(J2, N$2:N$17, O$2:O$17))

 

[shahsavand]

Hi,

Your example isn't completely clear to me (e.g. I don't know why you have -1 as the LookupValue for the Code 1 - shouldn't it be -20?).
If that was just a typo, then perhaps try something like this:

=IFERROR(
    SMALL(
      CHOOSE(
        {1,2,3},
        IF(A$2:A$8 = J2, B$2:B$8),
        IF(D$2:D$8 = J2, E$2:E$8),
        IF(G$2:G$8 = J2, H$2:H$8)),
      1),
    LOOKUP(J2, N$2:N$17, O$2:O$17))

You are aweeeeesome dear. Actually this formula helps me to calculate the quantity of replacement which I told you before.
I'm reaaally glad to meet you.
How can I be like you.is there any references for me to study and improve my excel skills?
In this table column B represent the first formula you gave to me.in column E I found a way to calculate the quantity of fist replacement and now with our latest legendary formula I can calculate the rest of.
I will let you know about the result.(I should try it in my office)

Please give me any advice which might be helpful to improve my excel skill.i wanna be like you