Material reporting - interchangeability - product line versus product depth

Ronan_1

New Member
Joined
Feb 16, 2017
Messages
10
Dear all,






I am trying to generate a material report for different product lines. Unfortunately, when creating the references in our system (different suppliers giving the same product specification on a different part number), the links between specific products was not made in a simple way and now I am struggling building the report as the data needs to be cleaned.


Here is my issue:


We have different product lines (example data): screws, nuts and bolts. Each of these product lines have different models with the same product specification all referenced with a specific part number (due to the different supplier).


My aim is to create a primary key for all parts that are so called interchangeable (same form, fit and function).


here under, is the extract of data I get (sample, real data is more than 20,000 lines)


As you will see, some parts have the same description but are not interchangeable.


I was trying to generate a model following the principle if A = B and B = C then A = C but i did not manage to reach my goal yet.






Would you be able to help me?






Thank you for your support!






Ronan






data extracted
PN1description1PN2description2
UE37screwDY42screw
MT75screw
YN66screw
PO87screw
UV75screw
DY42screwUE37screw
BP13screw
PW58screw
YN66screwYU44screw
PO87screw
UV75screw
UT14screwXL43screw
YD50screw
YN66screw
KM91nutRM13nut
GJ29nut
BK96nut
CZ89nut
EO41nut
CQ81nutVH75nut
TT67nut
KM91nut
KI49boltPN10bolt
HW91bolt
JL63bolt
TH20boltPR55bolt
PN10bolt
ZT51bolt
YE57bolt
BY10bolt
JL63bolt

<colgroup><col span="5"></colgroup><tbody>
</tbody>





Data expected after cleaning
PN1description1PN2description2Unique product line ID
UE37screwDY42screwS001
MT75screwS001
YN66screwS001
PO87screwS001
UV75screwS001
DY42screwUE37screwS001
BP13screwS001
PW58screwS001
YN66screwYU44screwS001
PO87screwS001
UV75screwS001
AX32screwZA55screwS002
XD40screwS002
AA23screwS002
KM91nutRM13nutN001
GJ29nutN001
BK96nutN001
CZ89nutN001
CQ81nutVH75nutN001
TT67nutN001
KM91nutN001
EO41nutOE23nutN002
KI49boltPN10boltB001
HW91boltB001
JL63boltB001
TH20boltPR55boltB001
PN10boltB001
ZT51boltB001
YE57boltB001
BY10boltB001
JL63boltB001

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi Everyone?

Would you have an idea how to solve this request?
I would really appreciate as I am completely blocked...
Thank you for your help!

Ronan
 
Upvote 0
Hi Bhos123,

thank you for your question.
S001 and S002 are two different product lines. They cannot be mixed at all as they have completely different specifications and attributes.
AX32 cannot be replaced by any of the PN shown in the S001 product line but it has alternates in the S002 product line, therefore it is belonging to S002.

Ronan
 
Upvote 0
Now you have classified products into S001, S002 , N001 , B001..etc. what exactly you want to do next?
 
Upvote 0
The thing is that I do not have this classification. I would like to generate it from the the input dataset (data extracted). S001, S002, ... shall be the new classification. Until now, I just have columns PN1, Descr. 1, PN2, Descr. 2.

Ronan
 
Upvote 0
I do not manage to get a way to automatically go through the PN1 - PN2 lists and generate this classification automatically.

The thing is that I do not have this classification. I would like to generate it from the the input dataset (data extracted). S001, S002, ... shall be the new classification. Until now, I just have columns PN1, Descr. 1, PN2, Descr. 2.

Ronan
 
Upvote 0
You can generate the classification using the algorithm that you want to use to classify. We first need to mention it very clearly. Then we can do the coding to generate them.
 
Last edited:
Upvote 0
Thank you for your answer. Is it possible to have a unique primary key that is automatically generated?
 
Upvote 0
Is your PN1 your primary key?? or PN2?. If you want to extract unique values from a column. First copy the column and paste it in some other column, then select it and select 'Remove duplicates' from 'Data>>Data Tolls'. you will get the unique values.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top