How to generate a Picking List of Components that are linked to a Product Code?

[HWall97]

Hi,

I currently have a sheet where order quantities can be entered for specific codes (please see 'Order Table'). These codes are for 'kits' which require several components to be made (please see 'Components Table'). Please see below for an example, the actual spreadsheet has about 23 different codes for 'kits.' The components that are required for each code are specified in the Components table (e.g. Code 21.1350 requires 1 x 123, 6 x 456, 20 x 789).

What I would like to have in the end, is a picking list for the components needed (in reality, for any given 'kit' we have there will be at least 10 components required). I want the picking list to look something like this if possible:

KIT QTY		CODE	DESCRIPTION	COMPONENT QTY
1	21.1350
		123	Pipe	1
		456	Cable Tie	6
		789	100mm Screw	20
2	21.1550
		123	Pipe	6
		456	Cable Tie	20
		789	100mm Screw	40

<tbody>
</tbody>

My problem is I don't know how to make it so the picking slip only has the kits that are selected, not all the kits possible as there would be too many. And, for example, I need it to be able to identify that there are 2 x 21.1550 kits so we will need double the components to be picked.

Your help would be greatly appreciated



Order Table:

QUANTITY	CODE	PRICE
1	21.1350	82.00
0	21.1450	136.00
2	21.1550	190.00

<tbody>
</tbody>

Components Table:

			21.1350	21.1450	21.1550
Code	Description	Price	1350mm Pipe	1450mm Pipe	1550 Pipe
123	Pipe	50.00	1	2	3
456	Cable Tie	2.00	6	8	10
789	100mm Screw	1.00	20	20	20

<tbody>
</tbody>