Kitting Spreadsheet

[DocRogers]

I'm trying to create a spread sheet to show what parts go into each of the kits we assemble. I then want to create a pivot table to determine how many of each part are used in a month to figure out the correct purchasing amount for each part. I'm not sure how to lay it out in excel. An example of the information would be:
Kit# and then the listed components and how many are used in each kit, then how many kits are sold each month. Just not sure how to lay it out so it will work easily with creating a pivot table. Any help is greatly appreciated. Thank you

 

[DocRogers]

Manufacturing Resource Planning (MRP) can get quite complicated but something quite useful can be set up very easily
and then enhanced later on.

Structuring the data well is crucial.

I assume that kits have a reference number and a name and likewise for components.
I refer to these references as numbers below but they could be a combination of alpha characters and numeric digits and even
characters such as hyphens.

Here is what I suggest to start with:

Worksheets:
Kits
Components
Assemblies (A list of components used in each kit.)

Kit Worksheet Columns:
Kit Number
Kit Name
Qty Sold Per Month

Component Worksheet Columns:
Component Number
Component Name
Qty needed per month (This is calculated using data in the Assemblies table)

Assemblies
Kit Number
Kit Name (Lookup on the Kit Worksheet)
Component Number
Component Name (Lookup on the Component Worksheet)
Number Of Components Required

Could it be the case that a 'Component' is made up of a number of other components.

Can you post some of your data using XL2BB.

Manufacturing Resource Planning (MRP) can get quite complicated but something quite useful can be set up very easily
and then enhanced later on.

Structuring the data well is crucial.

I assume that kits have a reference number and a name and likewise for components.
I refer to these references as numbers below but they could be a combination of alpha characters and numeric digits and even
characters such as hyphens.

Here is what I suggest to start with:

Worksheets:
Kits
Components
Assemblies (A list of components used in each kit.)

Kit Worksheet Columns:
Kit Number
Kit Name
Qty Sold Per Month

Component Worksheet Columns:
Component Number
Component Name
Qty needed per month (This is calculated using data in the Assemblies table)

Assemblies
Kit Number
Kit Name (Lookup on the Kit Worksheet)
Component Number
Component Name (Lookup on the Component Worksheet)
Number Of Components Required

Could it be the case that a 'Component' is made up of a number of other components.

Can you post some of your data using XL2BB.

So, I spoke with IT and I'm not allowed to install the XL2BB unfortunately. I am pasting in what I can from excel not sure if this will work.

 

[DocRogers]

KIT	Componets	Vendor Part #	qty per kit		KIT QTY 5	KIT QTY 10	KIT QTY 25
BD205-001
	BD205-1	403318702437-375B	2​	SPLINE BODY ID POLYPAK	10​	20​	50​
	BD205-2	403318702062-375B	1​	MANDREL EXTENSION OD POLYPAK	5​	10​	25​
	BD205-3	N03318702000-375B	3​	CONNECTOR BODY ID POLYPAK	15​	30​	75​
BD200-001
	BD205-1	403318702437-375B	2​	SPLINE BODY ID POLYPAK	10​	20​	50​
	BD205-2	403318702062-375B	2​	MANDREL EXTENSION OD POLYPAK	10​	20​	50​
	BD200-3	403318701750-375B	3​	CONNECTOR BODY ID POLYPAK	15​	30​	75​
	BD200-3	403318701750-375B	2​	FLOATER ID POLYPAK	10​	20​	50​
	BD200-4	403318702125-375B	2​	FLOATER OD POLYPAK	10​	20​	50​
BD201-001
	AQ29003-001	403318702750-375B	2​	CONNECTOR BODY OD POLYPAK	10​	20​	50​
	BD201-2	403318702625-375B	2​	MANDREL EXTENSION OD POLYPAK	10​	20​	50​
	BD201-2	403318702625-375B	2​	FLOATER OD POLYPAK	10​	20​	50​
	BD201-3	403318702250-375B	4​	CONNECTOR BODY ID POLYPAK	20​	40​	100​
	BD201-3	403318702250-375B	2​	FLOATER ID POLYPAK	10​	20​	50​
	BD202-3	403318703000-375B	2​	SPLINE BODY ID POLYPAK	10​	20​	50​
BD208-001
	BX144-3	403318703125-375B	2​	SPLINE BODY ID POLYPAK	10​	20​	50​
	AQ29003-001	403318702750-375B	2​	MANDREL EXTENSION OD POLYPAK	10​	20​	50​
	AQ29003-001	403318702750-375B	2​	CONNECTOR BODY OD POLYPAK	10​	20​	50​
	AQ29003-001	403318702750-375B	2​	FLOATER OD POLYPAK	10​	20​	50​
	BD208-3	403318702500-375B	4​	CONNECTOR BODY ID POLYPAK	20​	40​	100​
	BD208-3	403318702500-375B	2​	FLOATER ID POLYPAK	10​	20​	50​

 

[awoohaw]

Two questions:

PLEASE. What version of excel are you using, (my 2nd request)?

Why do you have two different descriptions for the same component in the same kit?:

	BD200-3	403318701750-375B	3	CONNECTOR BODY ID POLYPAK	15	30	75
	BD200-3	403318701750-375B	2	FLOATER ID POLYPAK	10	20	50

Your data is not good if this is accurate.

 

[Herakles]

hello, that is what i'm trying to do with this list. The customer gave us a list of kits, the components that go in them, how many of each component and how many they will be buying each month. I'm trying to create a pivot table to see how many of each component we will need to purchase to cover all the kits. Some kits use the same components as others and some components are used in only 1 kit. Once I know the totals I can then work on getting quotes from the vendors along with min buy quantities and or price breaks.

You dont need a pivot table to do this.

Do you have a list of which vendors sell each component or will you send the complete list of requirements to every vendor?

Adding another two tables to my model would enable th compilation of lists that you can send to the vendors to get quotes.

Vendor
Vendor ID
Vendor Name
Contact Name
Email Address

VendorComponents
Vendor ID
Vendor Name (Lookup on the Vendor Worksheet)
Component Number
Component Name (Lookup on the Component Worksheet)

A tight structure such as this will mean that your minimise the number of worksheets you need to
maintain the data and you can avoid duplication.

An enhancement of this would be a worksheet to contain the costs.

 

[awoohaw]

You dont need a pivot table to do this.

Do you have a list of which vendors sell each component or will you send the complete list of requirements to every vendor?

Adding another two tables to my model would enable th compilation of lists that you can send to the vendors to get quotes.

Vendor
Vendor ID
Vendor Name
Contact Name
Email Address

VendorComponents
Vendor ID
Vendor Name (Lookup on the Vendor Worksheet)
Component Number
Component Name (Lookup on the Component Worksheet)

A tight structure such as this will mean that your minimise the number of worksheets you need to
maintain the data and you can avoid duplication.

An enhancement of this would be a worksheet to contain the costs.

The OP has confusing "Data". I suggested something similar to this in an earlier post.
Until the OP cleans up the question and explains the data structure and tells us what version of excel is being used, then we are going in circles.

 

[Herakles]

KIT	Componets	Vendor Part #	qty per kit		KIT QTY 5	KIT QTY 10	KIT QTY 25
BD205-001
	BD205-1	403318702437-375B	2​	SPLINE BODY ID POLYPAK	10​	20​	50​
	BD205-2	403318702062-375B	1​	MANDREL EXTENSION OD POLYPAK	5​	10​	25​
	BD205-3	N03318702000-375B	3​	CONNECTOR BODY ID POLYPAK	15​	30​	75​
BD200-001
	BD205-1	403318702437-375B	2​	SPLINE BODY ID POLYPAK	10​	20​	50​
	BD205-2	403318702062-375B	2​	MANDREL EXTENSION OD POLYPAK	10​	20​	50​
	BD200-3	403318701750-375B	3​	CONNECTOR BODY ID POLYPAK	15​	30​	75​
	BD200-3	403318701750-375B	2​	FLOATER ID POLYPAK	10​	20​	50​
	BD200-4	403318702125-375B	2​	FLOATER OD POLYPAK	10​	20​	50​
BD201-001
	AQ29003-001	403318702750-375B	2​	CONNECTOR BODY OD POLYPAK	10​	20​	50​
	BD201-2	403318702625-375B	2​	MANDREL EXTENSION OD POLYPAK	10​	20​	50​
	BD201-2	403318702625-375B	2​	FLOATER OD POLYPAK	10​	20​	50​
	BD201-3	403318702250-375B	4​	CONNECTOR BODY ID POLYPAK	20​	40​	100​
	BD201-3	403318702250-375B	2​	FLOATER ID POLYPAK	10​	20​	50​
	BD202-3	403318703000-375B	2​	SPLINE BODY ID POLYPAK	10​	20​	50​
BD208-001
	BX144-3	403318703125-375B	2​	SPLINE BODY ID POLYPAK	10​	20​	50​
	AQ29003-001	403318702750-375B	2​	MANDREL EXTENSION OD POLYPAK	10​	20​	50​
	AQ29003-001	403318702750-375B	2​	CONNECTOR BODY OD POLYPAK	10​	20​	50​
	AQ29003-001	403318702750-375B	2​	FLOATER OD POLYPAK	10​	20​	50​
	BD208-3	403318702500-375B	4​	CONNECTOR BODY ID POLYPAK	20​	40​	100​
	BD208-3	403318702500-375B	2​	FLOATER ID POLYPAK	10​	20​	50​

The OP has confusing "Data". I suggested something similar to this in an earlier post.
Until the OP cleans up the question and explains the data structure and tells us what version of excel is being used, then we are going in circles.

I agree but I don't think that the OP knows about normalisation.

If the data is structured properly, the first job to accomplish, then the production of what he is after will be relatively easy.

 

[DocRogers]

Two questions:

PLEASE. What version of excel are you using, (my 2nd request)?

Why do you have two different descriptions for the same component in the same kit?:

	BD200-3	403318701750-375B	3	CONNECTOR BODY ID POLYPAK	15	30	75
	BD200-3	403318701750-375B	2	FLOATER ID POLYPAK	10	20	50

Your data is not good if this is accurate.

Sorry didn't see your request about the Excel version. We use Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20298) 32-bit . As to the description, the kit uses the same part but in different locations within the final build so they call it two different things but we will actually get rid of the description and only use the part number. So in this kit it would contain 5 of these polypaks.

 

[Herakles]

KIT	Componets	Vendor Part #	qty per kit		KIT QTY 5	KIT QTY 10	KIT QTY 25
BD205-001
	BD205-1	403318702437-375B	2​	SPLINE BODY ID POLYPAK	10​	20​	50​
	BD205-2	403318702062-375B	1​	MANDREL EXTENSION OD POLYPAK	5​	10​	25​
	BD205-3	N03318702000-375B	3​	CONNECTOR BODY ID POLYPAK	15​	30​	75​
BD200-001
	BD205-1	403318702437-375B	2​	SPLINE BODY ID POLYPAK	10​	20​	50​
	BD205-2	403318702062-375B	2​	MANDREL EXTENSION OD POLYPAK	10​	20​	50​
	BD200-3	403318701750-375B	3​	CONNECTOR BODY ID POLYPAK	15​	30​	75​
	BD200-3	403318701750-375B	2​	FLOATER ID POLYPAK	10​	20​	50​
	BD200-4	403318702125-375B	2​	FLOATER OD POLYPAK	10​	20​	50​
BD201-001
	AQ29003-001	403318702750-375B	2​	CONNECTOR BODY OD POLYPAK	10​	20​	50​
	BD201-2	403318702625-375B	2​	MANDREL EXTENSION OD POLYPAK	10​	20​	50​
	BD201-2	403318702625-375B	2​	FLOATER OD POLYPAK	10​	20​	50​
	BD201-3	403318702250-375B	4​	CONNECTOR BODY ID POLYPAK	20​	40​	100​
	BD201-3	403318702250-375B	2​	FLOATER ID POLYPAK	10​	20​	50​
	BD202-3	403318703000-375B	2​	SPLINE BODY ID POLYPAK	10​	20​	50​
BD208-001
	BX144-3	403318703125-375B	2​	SPLINE BODY ID POLYPAK	10​	20​	50​
	AQ29003-001	403318702750-375B	2​	MANDREL EXTENSION OD POLYPAK	10​	20​	50​
	AQ29003-001	403318702750-375B	2​	CONNECTOR BODY OD POLYPAK	10​	20​	50​
	AQ29003-001	403318702750-375B	2​	FLOATER OD POLYPAK	10​	20​	50​
	BD208-3	403318702500-375B	4​	CONNECTOR BODY ID POLYPAK	20​	40​	100​
	BD208-3	403318702500-375B	2​	FLOATER ID POLYPAK	10​	20​	50​

Dont have any blank cells in your data or blank lines.

This is raw data and not for presentation purposes.

You need to get the data model right and documented before start to populate worksheets.

What do these columns represent?

KIT QTY 5
KIT QTY 10
KIT QTY 25

Book1
	A
2	BD205-001
Assemblies

 

[Fluff]

We use Microsoft® Excel® for Microsoft 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Herakles]

Dont have any blank cells in your data or blank lines.

This is raw data and not for presentation purposes.

You need to get the data model right and documented before start to populate worksheets.

What do these columns represent?

KIT QTY 5
KIT QTY 10
KIT QTY 25

Book1
	A
2	BD205-001
Assemblies

Data without blank cells / rows.

Kitting Spreadsheet.xlsm
	A	B	C	D	E	F	G	H
1	KIT	Componets	Vendor Part #	qty per kit	Component Name	KIT QTY 5	KIT QTY 10	KIT QTY 25
2	BD205-001	BD205-1	403318702437-375B	2	SPLINE BODY ID POLYPAK	10	20	50
3	BD205-001	BD205-2	403318702062-375B	1	MANDREL EXTENSION OD POLYPAK	5	10	25
4	BD205-001	BD205-3	N03318702000-375B	3	CONNECTOR BODY ID POLYPAK	15	30	75
5	BD200-001	BD205-1	403318702437-375B	2	SPLINE BODY ID POLYPAK	10	20	50
6	BD200-001	BD205-2	403318702062-375B	2	MANDREL EXTENSION OD POLYPAK	10	20	50
7	BD200-001	BD200-3	403318701750-375B	3	CONNECTOR BODY ID POLYPAK	15	30	75
8	BD200-001	BD200-3	403318701750-375B	2	FLOATER ID POLYPAK	10	20	50
9	BD200-001	BD200-4	403318702125-375B	2	FLOATER OD POLYPAK	10	20	50
10	BD201-001	AQ29003-001	403318702750-375B	2	CONNECTOR BODY OD POLYPAK	10	20	50
11	BD201-001	BD201-2	403318702625-375B	2	MANDREL EXTENSION OD POLYPAK	10	20	50
12	BD201-001	BD201-2	403318702625-375B	2	FLOATER OD POLYPAK	10	20	50
13	BD201-001	BD201-3	403318702250-375B	4	CONNECTOR BODY ID POLYPAK	20	40	100
14	BD201-001	BD201-3	403318702250-375B	2	FLOATER ID POLYPAK	10	20	50
15	BD201-001	BD202-3	403318703000-375B	2	SPLINE BODY ID POLYPAK	10	20	50
16	BD208-001	BX144-3	403318703125-375B	2	SPLINE BODY ID POLYPAK	10	20	50
17	BD208-001	AQ29003-001	403318702750-375B	2	MANDREL EXTENSION OD POLYPAK	10	20	50
18	BD208-001	AQ29003-001	403318702750-375B	2	CONNECTOR BODY OD POLYPAK	10	20	50
19	BD208-001	AQ29003-001	403318702750-375B	2	FLOATER OD POLYPAK	10	20	50
20	BD208-001	BD208-3	403318702500-375B	4	CONNECTOR BODY ID POLYPAK	20	40	100
21	BD208-001	BD208-3	403318702500-375B	2	FLOATER ID POLYPAK	10	20	50
Assemblies