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

 

[awoohaw]

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.

Consistency is key. Not sure how much control you have over you "locations" but if it is your process and your responsibility my suggestion would be to get rid of the amibiguous columns as soon as you can. Why even have that listed in your KITs if it is for supply requisitioning? And why have it listed mulitiple times.
But that really doesn't affect the question too much. But you need to convert this "report" into a data structure before you can even consider using it in pivot table.

1. The KIT number must be on each row for each component.
2. Extraneous columns need to be removed (the ambiguous description is worthless in your request - but you may need it for other reporting but that is outside of scope your question).
3. You need to data sources

1. Single KIT components
2. Sales/Orders

365 is helpful.

I would also suggest you look into using POWER query.

 

[awoohaw]

Okay, here is what I've come up with. No Pivot:

Mr excel questions 42.xlsm
	A	B	C	D	E	F	G	H
62	Your data, filling in Kit ID and removing unnecessary rows:
63
64	KIT	Componets	Vendor Part #	qty per kit		KIT QTY 5	KIT QTY 10	KIT QTY 25
65	BD205-001	BD205-1	403318702437-375B	2	SPLINE BODY ID POLYPAK	10	20	50
66	BD205-001	BD205-2	403318702062-375B	1	MANDREL EXTENSION OD POLYPAK	5	10	25
67	BD205-001	BD205-3	N03318702000-375B	3	CONNECTOR BODY ID POLYPAK	15	30	75
68	BD200-001	BD205-1	403318702437-375B	2	SPLINE BODY ID POLYPAK	10	20	50
69	BD200-001	BD205-2	403318702062-375B	2	MANDREL EXTENSION OD POLYPAK	10	20	50
70	BD200-001	BD200-3	403318701750-375B	3	CONNECTOR BODY ID POLYPAK	15	30	75
71	BD200-001	BD200-3	403318701750-375B	2	FLOATER ID POLYPAK	10	20	50
72	BD200-001	BD200-4	403318702125-375B	2	FLOATER OD POLYPAK	10	20	50
73	BD201-001	AQ29003-001	403318702750-375B	2	CONNECTOR BODY OD POLYPAK	10	20	50
74	BD201-001	BD201-2	403318702625-375B	2	MANDREL EXTENSION OD POLYPAK	10	20	50
75	BD201-001	BD201-2	403318702625-375B	2	FLOATER OD POLYPAK	10	20	50
76	BD201-001	BD201-3	403318702250-375B	4	CONNECTOR BODY ID POLYPAK	20	40	100
77	BD201-001	BD201-3	403318702250-375B	2	FLOATER ID POLYPAK	10	20	50
78	BD201-001	BD202-3	403318703000-375B	2	SPLINE BODY ID POLYPAK	10	20	50
79	BD208-001	BX144-3	403318703125-375B	2	SPLINE BODY ID POLYPAK	10	20	50
80	BD208-001	AQ29003-001	403318702750-375B	2	MANDREL EXTENSION OD POLYPAK	10	20	50
81	BD208-001	AQ29003-001	403318702750-375B	2	CONNECTOR BODY OD POLYPAK	10	20	50
82	BD208-001	AQ29003-001	403318702750-375B	2	FLOATER OD POLYPAK	10	20	50
83	BD208-001	BD208-3	403318702500-375B	4	CONNECTOR BODY ID POLYPAK	20	40	100
84	BD208-001	BD208-3	403318702500-375B	2	FLOATER ID POLYPAK	10	20	50
85
86	Unique kits and components.
87	BD205-001	BD205-1	2
88	BD205-001	BD205-2	1
89	BD205-001	BD205-3	3
90	BD200-001	BD205-1	2
91	BD200-001	BD205-2	2
92	BD200-001	BD200-3	5
93	BD200-001	BD200-4	2
94	BD201-001	AQ29003-001	2
95	BD201-001	BD201-2	4
96	BD201-001	BD201-3	6
97	BD201-001	BD202-3	2
98	BD208-001	BX144-3	2
99	BD208-001	AQ29003-001	6
100	BD208-001	BD208-3	6
101
102
103
104	Kit	Order Size
105	BD205-001	15
106	BD200-001	25
107	BD201-001	35
108	BD208-001	50
109
110
111
112	Unique kits and components orders
113	BD205-001	BD205-1	30
114	BD205-001	BD205-2	15
115	BD205-001	BD205-3	45
116	BD200-001	BD205-1	50
117	BD200-001	BD205-2	50
118	BD200-001	BD200-3	125
119	BD200-001	BD200-4	50
120	BD201-001	AQ29003-001	70
121	BD201-001	BD201-2	140
122	BD201-001	BD201-3	210
123	BD201-001	BD202-3	70
124	BD208-001	BX144-3	100
125	BD208-001	AQ29003-001	300
126	BD208-001	BD208-3	300
127
128
129	unique components
130	BD205-1	80
131	BD205-2	65
132	BD205-3	45
133	BD200-3	125
134	BD200-4	50
135	AQ29003-001	370
136	BD201-2	140
137	BD201-3	210
138	BD202-3	70
139	BX144-3	100
140	BD208-3	300
141
DocRogers

Cell Formulas
Range	Formula
A87:B100,A113:B126	A87	=UNIQUE($A$65:$B$84)
C87:C100	C87	=SUM(($A$65:$A$84=A87)*($B$65:$B$84=B87)*($D$65:$D$84))
A105:A108	A105	=UNIQUE($A$65:$A$84)
C113:C126	C113	=SUM(($A$65:$A$84=A113)*($B$65:$B$84=B113)*($D$65:$D$84))*XLOOKUP(A113,$A$105#,$B$105:$B$108)
A130:A140	A130	=UNIQUE($B$87:$B$100)
B130:B140	B130	=SUM(($B$113:$B$126=A130)*($C$113:$C$126))
Dynamic array formulas.

 

[DocRogers]

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

Those cells are from my inside salesperson and will be removed. Sorry

 

[DocRogers]

Okay, here is what I've come up with. No Pivot:

Mr excel questions 42.xlsm
	A	B	C	D	E	F	G	H
62	Your data, filling in Kit ID and removing unnecessary rows:
63
64	KIT	Componets	Vendor Part #	qty per kit		KIT QTY 5	KIT QTY 10	KIT QTY 25
65	BD205-001	BD205-1	403318702437-375B	2	SPLINE BODY ID POLYPAK	10	20	50
66	BD205-001	BD205-2	403318702062-375B	1	MANDREL EXTENSION OD POLYPAK	5	10	25
67	BD205-001	BD205-3	N03318702000-375B	3	CONNECTOR BODY ID POLYPAK	15	30	75
68	BD200-001	BD205-1	403318702437-375B	2	SPLINE BODY ID POLYPAK	10	20	50
69	BD200-001	BD205-2	403318702062-375B	2	MANDREL EXTENSION OD POLYPAK	10	20	50
70	BD200-001	BD200-3	403318701750-375B	3	CONNECTOR BODY ID POLYPAK	15	30	75
71	BD200-001	BD200-3	403318701750-375B	2	FLOATER ID POLYPAK	10	20	50
72	BD200-001	BD200-4	403318702125-375B	2	FLOATER OD POLYPAK	10	20	50
73	BD201-001	AQ29003-001	403318702750-375B	2	CONNECTOR BODY OD POLYPAK	10	20	50
74	BD201-001	BD201-2	403318702625-375B	2	MANDREL EXTENSION OD POLYPAK	10	20	50
75	BD201-001	BD201-2	403318702625-375B	2	FLOATER OD POLYPAK	10	20	50
76	BD201-001	BD201-3	403318702250-375B	4	CONNECTOR BODY ID POLYPAK	20	40	100
77	BD201-001	BD201-3	403318702250-375B	2	FLOATER ID POLYPAK	10	20	50
78	BD201-001	BD202-3	403318703000-375B	2	SPLINE BODY ID POLYPAK	10	20	50
79	BD208-001	BX144-3	403318703125-375B	2	SPLINE BODY ID POLYPAK	10	20	50
80	BD208-001	AQ29003-001	403318702750-375B	2	MANDREL EXTENSION OD POLYPAK	10	20	50
81	BD208-001	AQ29003-001	403318702750-375B	2	CONNECTOR BODY OD POLYPAK	10	20	50
82	BD208-001	AQ29003-001	403318702750-375B	2	FLOATER OD POLYPAK	10	20	50
83	BD208-001	BD208-3	403318702500-375B	4	CONNECTOR BODY ID POLYPAK	20	40	100
84	BD208-001	BD208-3	403318702500-375B	2	FLOATER ID POLYPAK	10	20	50
85
86	Unique kits and components.
87	BD205-001	BD205-1	2
88	BD205-001	BD205-2	1
89	BD205-001	BD205-3	3
90	BD200-001	BD205-1	2
91	BD200-001	BD205-2	2
92	BD200-001	BD200-3	5
93	BD200-001	BD200-4	2
94	BD201-001	AQ29003-001	2
95	BD201-001	BD201-2	4
96	BD201-001	BD201-3	6
97	BD201-001	BD202-3	2
98	BD208-001	BX144-3	2
99	BD208-001	AQ29003-001	6
100	BD208-001	BD208-3	6
101
102
103
104	Kit	Order Size
105	BD205-001	15
106	BD200-001	25
107	BD201-001	35
108	BD208-001	50
109
110
111
112	Unique kits and components orders
113	BD205-001	BD205-1	30
114	BD205-001	BD205-2	15
115	BD205-001	BD205-3	45
116	BD200-001	BD205-1	50
117	BD200-001	BD205-2	50
118	BD200-001	BD200-3	125
119	BD200-001	BD200-4	50
120	BD201-001	AQ29003-001	70
121	BD201-001	BD201-2	140
122	BD201-001	BD201-3	210
123	BD201-001	BD202-3	70
124	BD208-001	BX144-3	100
125	BD208-001	AQ29003-001	300
126	BD208-001	BD208-3	300
127
128
129	unique components
130	BD205-1	80
131	BD205-2	65
132	BD205-3	45
133	BD200-3	125
134	BD200-4	50
135	AQ29003-001	370
136	BD201-2	140
137	BD201-3	210
138	BD202-3	70
139	BX144-3	100
140	BD208-3	300
141
DocRogers

Cell Formulas
Range	Formula
A87:B100,A113:B126	A87	=UNIQUE($A$65:$B$84)
C87:C100	C87	=SUM(($A$65:$A$84=A87)*($B$65:$B$84=B87)*($D$65:$D$84))
A105:A108	A105	=UNIQUE($A$65:$A$84)
C113:C126	C113	=SUM(($A$65:$A$84=A113)*($B$65:$B$84=B113)*($D$65:$D$84))*XLOOKUP(A113,$A$105#,$B$105:$B$108)
A130:A140	A130	=UNIQUE($B$87:$B$100)
B130:B140	B130	=SUM(($B$113:$B$126=A130)*($C$113:$C$126))
Dynamic array formulas.

Let me play with this and i will let you know any feed back. thank you so much for the help.

 

[DocRogers]

Thank you all for your suggestions. I've managed to make this work. Thank you