stock card to excel

[babytsina]

good day, i'm trying to convert our family business stock card to excel but having difficulties, exactly i have no idea on what formula should i use to achieve this, here are a few details on our stock card

date	inventory in	inventory out	Stocks on Hand
12/25/2017	44		44
12/26/2017		16	28
12/26/2017		5	23
12/27/2017	20	2	41
12/28/2017		1	40
12/29/2017	2		42
1/2/2018		5	37
1/3/2018	10	5	42
1/3/2018	1	6	37

<tbody>
</tbody>

hope somebody could help me, thanks in advance

 

[AhoyNC]

Maybe something like this.
Copy formula in D3 down as needed.

Excel Workbook
	A	B	C	D
1	date	inventory in	inventory out	Stocks on Hand
2	12/25/2017	44		44
3	12/26/2017		16	28
4	12/26/2017		5	23
5	12/27/2017	20	2	41
6	12/28/2017		1	40
7	12/29/2017	2		42
8	1/2/2018		5	37
9	1/3/2018	10	5	42
10	1/3/2018	1	6	37
Sheet

 

[babytsina]

thank you very much for the reply this definitely help helps me a lot . . . second question is how do you use this to automate a table or do i need new formula coz this shows an inconsistent formula when applied...

 

[Stiuart_W]

thank you very much for the reply this definitely help helps me a lot . . . second question is how do you use this to automate a table or do i need new formula coz this shows an inconsistent formula when applied...

You'll need to give a lot more information. Presumably you mean how to apply this formula to your other items in stock? But as we don't know how they are arranged in your Excel Workbook it's impossible to help

 

[babytsina]

You'll need to give a lot more information. Presumably you mean how to apply this formula to your other items in stock? But as we don't know how they are arranged in your Excel Workbook it's impossible to help

sorry for not being clear...you're right i was trying to apply this on items, but you see after i applied the said formula i've been getting the "inconsistent calculated column formula" warning, btw here is the arrangement in my workbook...

Date	Supplier Name	Received Quantity	OS number	Customer Name	Order Quantity	Stocks on Hand
26-Dec-17	Ubertech	44	205210	MarsHill	16	28
26-Dec-17			205211	YC PC	5	23
27-Dec-17	Ubertech	20	205566	HyperDash	2	41
28-Dec-17			205568	Computer	1	40
29-Dec-17	Ubertech	2				42
02-Jan-18			205275	MarsHill	5	37
03-Jan-18	Ubertech	10	205276	MarsHill	5	42
03-Jan-18	Ubertech	1	205294	YC PC	6	37
04-Jan-18			205581	Juvmar	1	36
05-Jan-18			205585	MarsHill	5	31
05-Jan-18			205586	MarsHill	10	21
06-Jan-18	Ubertech	5	205592	MarsHill	5	21
06-Jan-18			205593	Juvmar	2	19
09-Jan-18	Ubertech	26	205906	MarsHill	15	30
09-Jan-18	Ubertech	12	205250	DJ7 Summit	1	41
09-Jan-18			205904	Kedeem	12	29
10-Jan-18	Ubertech	15	205914	HuperDash	4	40
10-Jan-18	Ubertech	10	205913	MarsHill	10	40
11-Jan-18			205917	J Maxx	10	30
12-Jan-18			205927	HyperDash	10	20
12-Jan-18			205240	JOY Computers	2	18
13-Jan-18	Ubertech	26	205934	HyperDash	2	42
15-Jan-18			205933	MarsHill	10	32
15-Jan-18			205790	Edsan	10	22

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

i hope this helps, and again thank you for accommodating me with this dilemma of min

 

[Stiuart_W]

That doesn't look like your original post.

Is "Order Quantity" the same as "inventory Out"?

I don't see any duplicate part numbers but presumably in the full list the part numbers do duplicate. And presumably the numbers in the "Stocks on hand" are incorrect as it doesn't make any sense to reduce the stock of different part numbers

Perhaps you should upload an example file to dropbox or similar?

 

[babytsina]

thank you for the fast response, as a matter of fact it is "Order Quantity" is the "Inventory Out", the thing is the list is a day to day inventory of an item, inventory in nor inventory out wise, if you could total all the "received quantity" minus all "order quantity" you'll get "22" just like on the very last entry of the stocks on hand, this is how i monitor the day to day flow of an item, it seems old habits does not change overnight, if you could direct me on an easier way past this one around i'll be thank'n you a lot, in regards with a sample file, ill just reg for a dropbox account and send it right away.....again thank you

 

[Stiuart_W]

Isn't "OS Number" the same as a part number? In that case how is sending out 10 of 205933 the same as 10 of 205790? You're going to have to explain the difference.

 

[babytsina]

oppsss... my bad please omit "OS Number" column, Order Slip(OS) Number is an another means on how we track where we sold a certain item to a customer, btw i hope this help, could upload the worksheet that i've been doing no dropbox was installed in this old unit.

Date	Supplier Name	Received Quantity	Customer Name	Order Quantity	Stocksa on Hand
12/26/17	Ubertech	44	MarsHill	16	28
12/26/17			YC PC	5	23
12/27/17	Ubertech	20	HyperDash	2	41
12/28/17			Computer Trade	1	40
12/29/17	Ubertech	2			42
01/02/18			MarsHill	5	37
01/03/18	Ubertech	10	MarsHill	5	42
01/03/18	Ubertech	1	YC PC	6	37
01/04/18			Juvmar	1	36
01/05/18			MarsHill	5	31
01/05/18			MarsHill	10	21
01/06/18	Ubertech	5	MarsHill	5	21
01/06/18			Juvmar	2	19
01/09/18	Ubertech	26	MarsHill	15	30
01/09/18	Ubertech	12	DJ7 Summit	1	41
01/09/18			Kedeem	12	29
01/10/18	Ubertech	15	HyperDash	4	40
01/10/18	Ubertech	10	MarsHill	10	40
01/11/18			J Maxx	10	30
01/12/18			HyperDash	10	20
01/12/18			JOY Computers	2	18
01/13/18	Ubertech	26	HyperDash	2	42
01/15/18			MarsHill	10	32
01/15/18			Edsan Broadband	10	22

<tbody>
</tbody>

again thank you for being so accommodating.

 

[babytsina]

oppsss....my bad, please omit "OS Number", Order Slip (OS) Number is another means for me to track who bought the certain item, please omit that one,
it should only be,

Date

Supplier Name

Received Quantity

Customer Name

Order Quantity

Stocks on

<tbody>
</tbody>

again thank you for being so accommodating.