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

[shinmaster19]

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

[p24leclerc]

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

[shinmaster19]

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.

[p24leclerc]

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?