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>

 

[circledchicken]

Hi,

Perhaps you can try a formula approach. Something like this in cell E2 and copy down:

=IF(
    ISNUMBER(MATCH(B2, B$1:B1, 0)),
    VLOOKUP(B2, B$1:E1, 4, 0),
    D2)
 - C2

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	65	69	100	31
12	C	3	54	12	-67
13	C	14	265	88	-177
14	C	26	32	99	67
Sheet1

 

[shahsavand]

Thank you for your help but it doesn't work when my id recur in more than two rows.

 

[shahsavand]

Hi,

Perhaps you can try a formula approach. Something like this in cell E2 and copy down:

=IF(
    ISNUMBER(MATCH(B2, B$1:B1, 0)),
    VLOOKUP(B2, B$1:E1, 4, 0),
    D2)
 - C2

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	65	69	100	31
12	C	3	54	12	-67
13	C	14	265	88	-177
14	C	26	32	99	67
Sheet1

Please help me

 

[circledchicken]

Oops, sorry, perhaps try this version instead:

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

 

[shahsavand]

Oops, sorry, perhaps try this version instead:

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

it's not working.the result is as per below table.
for example Id no 1: 5000-200=4800 which is correct.
4800-54=4746 is also correct.but third one must be like 4746-69=4677 but instead it will count like this: 4746-(69-54)=4731

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	4746
B	2	6	200	-6
c	1	69	100	4731
c	1	54	12	4746
c	1	265	5000	4535
c	1	32	5000	4768

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

please help me.thank you dear

 

[circledchicken]

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
Sheet1

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

 

[shahsavand]

you are Awesome.thank you.thank you very very much.now it's working.
god bless you dear

 

[circledchicken]

You're welcome - thanks for the feedback!

 

[shahsavand]

You're welcome - thanks for the feedback!