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
Bookmarks