stock card to excel

B

babytsina

New Member
Joined
Jan 24, 2018
Messages
6
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

[TABLE="width: 357"]
<tbody>[TR]
[TD]date[/TD]
[TD]inventory in[/TD]
[TD]inventory out[/TD]
[TD]Stocks on Hand[/TD]
[/TR]
[TR]
[TD="align: right"]12/25/2017[/TD]
[TD="align: right"]44[/TD]
[TD][/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="align: right"]12/26/2017[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]12/26/2017[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="align: right"]12/27/2017[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD="align: right"]12/28/2017[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]12/29/2017[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2018[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2018[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]37[/TD]
[/TR]
</tbody>[/TABLE]

hope somebody could help me, thanks in advance
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Maybe something like this.
Copy formula in D3 down as needed.
Excel Workbook
ABCD
1dateinventory ininventory outStocks on Hand
212/25/20174444
312/26/20171628
412/26/2017523
512/27/201720241
612/28/2017140
712/29/2017242
81/2/2018537
91/3/201810542
101/3/20181637
Sheet
 
Upvote 0
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...
 
Upvote 0
babytsina said:
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...
Click to expand...


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
 
Upvote 0
Stiuart_W said:
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
Click to expand...

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...

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

[/TD]
[/TR]
</tbody>[/TABLE]

i hope this helps, and again thank you for accommodating me with this dilemma of min
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.


<tbody>
[TD="class: xl71"]Date[/TD]
[TD="class: xl69, width: 64"]Supplier Name[/TD]
[TD="class: xl69, width: 64"]Received Quantity[/TD]
[TD="class: xl69, width: 131"]Customer Name[/TD]
[TD="class: xl69, width: 64"]Order Quantity[/TD]
[TD="class: xl70, width: 64"]Stocksa on Hand[/TD]

[TD="class: xl73"]12/26/17[/TD]
[TD="class: xl65"]Ubertech[/TD]
[TD="class: xl65"]44[/TD]
[TD="class: xl65"]MarsHill[/TD]
[TD="class: xl65"]16[/TD]
[TD="class: xl66"]28[/TD]

[TD="class: xl73"]12/26/17[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]YC PC[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl66"]23[/TD]

[TD="class: xl73"]12/27/17[/TD]
[TD="class: xl65"]Ubertech[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]HyperDash[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl66"]41[/TD]

[TD="class: xl73"]12/28/17[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Computer Trade[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl66"]40[/TD]

[TD="class: xl73"]12/29/17[/TD]
[TD="class: xl65"]Ubertech[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]42[/TD]

[TD="class: xl73"]01/02/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]MarsHill[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl66"]37[/TD]

[TD="class: xl73"]01/03/18[/TD]
[TD="class: xl65"]Ubertech[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]MarsHill[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl66"]42[/TD]

[TD="class: xl73"]01/03/18[/TD]
[TD="class: xl65"]Ubertech[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]YC PC[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl66"]37[/TD]

[TD="class: xl73"]01/04/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Juvmar[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl66"]36[/TD]

[TD="class: xl73"]01/05/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]MarsHill[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl66"]31[/TD]

[TD="class: xl73"]01/05/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]MarsHill[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl66"]21[/TD]

[TD="class: xl73"]01/06/18[/TD]
[TD="class: xl65"]Ubertech[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]MarsHill[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl66"]21[/TD]

[TD="class: xl73"]01/06/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Juvmar[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl66"]19[/TD]

[TD="class: xl73"]01/09/18[/TD]
[TD="class: xl65"]Ubertech[/TD]
[TD="class: xl65"]26[/TD]
[TD="class: xl65"]MarsHill[/TD]
[TD="class: xl65"]15[/TD]
[TD="class: xl66"]30[/TD]

[TD="class: xl73"]01/09/18[/TD]
[TD="class: xl65"]Ubertech[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]DJ7 Summit[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl66"]41[/TD]

[TD="class: xl73"]01/09/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Kedeem[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl66"]29[/TD]

[TD="class: xl73"]01/10/18[/TD]
[TD="class: xl65"]Ubertech[/TD]
[TD="class: xl65"]15[/TD]
[TD="class: xl65"]HyperDash[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl66"]40[/TD]

[TD="class: xl73"]01/10/18[/TD]
[TD="class: xl65"]Ubertech[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]MarsHill[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl66"]40[/TD]

[TD="class: xl73"]01/11/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]J Maxx[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl66"]30[/TD]

[TD="class: xl73"]01/12/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]HyperDash[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl66"]20[/TD]

[TD="class: xl73"]01/12/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]JOY Computers[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl66"]18[/TD]

[TD="class: xl73"]01/13/18[/TD]
[TD="class: xl65"]Ubertech[/TD]
[TD="class: xl65"]26[/TD]
[TD="class: xl65"]HyperDash[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl66"]42[/TD]

[TD="class: xl73"]01/15/18[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]MarsHill[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl66"]32[/TD]

[TD="class: xl74"]01/15/18[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"]Edsan Broadband[/TD]
[TD="class: xl67"]10[/TD]
[TD="class: xl68"]22[/TD]

</tbody>

again thank you for being so accommodating.
 
Upvote 0
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,
[TABLE="class: cms_table, width: 512"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Supplier Name
[/TD]
[TD]Received Quantity
[/TD]
[TD][/TD]
[TD]Customer Name[/TD]
[TD]Order Quantity
[/TD]
[TD]Stocks on
[/TD]
[/TR]
</tbody>[/TABLE]
again thank you for being so accommodating.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
397
etaf
etaf
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
496
etaf
etaf
H
  • Solved
Delete all rows including cells in column "A" that are identical to the values in cells "D1", "E1","F1" and "A6".
2
Replies
10
Views
565
Peter_SSs
Peter_SSs
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
269
Cquake
C
S
  • Question Question
Inventory disposal plan
Replies
3
Views
277
Spaceballs_the_fanboy
S

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top