+ Reply to Thread
Results 1 to 4 of 4

problem with an order with multiple entries for creating inventory stock system

  1. #1
    Registered User
    Join Date
    04-09-2015
    Location
    australia
    MS-Off Ver
    2013
    Posts
    2

    Exclamation problem with an order with multiple entries for creating inventory stock system

    Hi guys,

    I am trying to make an inventory database for textile business using excel 2013, however, I can't get my head around this problem.

    I am trying to create a pivot table based on Purchase order (PO) and sales order (SO). However, I kept failing to relate them. Tried to make unique ID but still failed.

    in one PO you will get 1 unique code from supplier with a few items. let's say. S123 (red 23,blue 24, white 12). and you will get different PO with different unique code from supplier but with same item code. so you might see, s123 (red 23, blue 24, white 12) and q123 (red 23, blue 24, black 10). so there is bound to be repetition in the item and each item have specific unique code (red 23 - s123 , red 23 - q123) with different stock amount. And each item stock might be sold partly e.g. red 24 - s123 with qty 12 sold to A for #3 and for B #4 so red 23-s123 left with #5

    table form columns:

    PURCHASE

    PO #
    unique supplier code
    item (colour, type of item, qty bought)
    comment

    SALES

    SO #
    buyer name
    unique supplier code
    item (colour, type of item, qty sold, qty return)
    comment

    the problem is that I would not know how many buyer it takes to get each item with specific code to be sold out. It is impractical for me to keep on making colum on the same row of the item.
    therefore, I would like to make use of pivot table. the problem is I can't make a relation between them although I thought the supplier unique code would do it as primary key; the reason being is that I do not know how to input each item so that it connects to the supplier code. e.g. S123 in column A1 red 23 B1 blue 24 B2 white 12 B3.
    if i tried making multiple rows of S123 so it correspond to the item rows, I would make duplicate value of supplier code and unsatisfied the requirement being a unique primary key.

    my end purpose is that I would like to see which item still have stock remaining, that said I need to list down colours with its type and code and see its qty remaining.
    I am hoping to do this through excel 2013 as I have yet to learn ms access.

    Hope I explained myself clearly.

    Thank you in advance. please help me anyone.


    edited:
    here I attached the kind of sample I am trying.
    ideally that's the kind of pivot table I am looking for. but the master(input) table contains redundancy from repetition of data as different items might come from the same supplier code and #PO and customer sales. and I know it looks totally wrong but I want to show the kind of thing I want to reduce. it contains redundancy.
    thus, I would like to separate the customer from that table and making a relational table between purchase and sales and enabling a unique code (supplier code) to include different items that come with the code
    Attached Files Attached Files
    Last edited by shinmaster19; 04-10-2015 at 01:53 AM. Reason: attaching workbook

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2003-2010-2013
    Posts
    2,078

    Re: problem with an order with multiple entries for creating inventory stock system

    Clear, it is not. Could you attached your workbook to yourpost?
    We could start by looking at your data structure and that could help understand what you want to do.
    Thanks
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Registered User
    Join Date
    04-09-2015
    Location
    australia
    MS-Off Ver
    2013
    Posts
    2

    Re: problem with an order with multiple entries for creating inventory stock system

    hi, I have attached the workbook.

    i know it looks messy. but that's the whole point; i would like to reduce that redundancy and could not find the way.

  4. #4
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2003-2010-2013
    Posts
    2,078

    Re: problem with an order with multiple entries for creating inventory stock system

    I think you need to structure your data using more sheets.
    One sheet for your supplier. Another one for your customer. Perhaps one for your material database which could also contain the inventory.
    One sheet for Sales.
    One sheet for Purchasing.
    Each entry row should have its date.
    You could use Vlookup instead of threaded IF statement for the material type.
    I think formulas could be used to keep your inventory up to date with Sales and Purchase.
    Pivot table could be interesting to analyse your Sales or your Purchasing but
    not necessarily for your inventory.
    What do you think?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Stock order system
    By finditmax in forum Excel General
    Replies: 1
    Last Post: 03-09-2015, 07:52 AM
  2. Creating a basic stock inventory sheet
    By 72saints72 in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 07-27-2014, 05:44 AM
  3. Creating data for my system inventory
    By uditgoyal in forum Excel General
    Replies: 3
    Last Post: 07-20-2014, 03:49 PM
  4. I need a stock order sheet to be created from a stock inventory
    By tamhav in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-20-2014, 10:40 AM
  5. Creating an automatic stock list/inventory, HELP!
    By ibz1000 in forum Excel General
    Replies: 1
    Last Post: 08-01-2013, 05:35 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1