Material reporting - interchangeability - product line versus product depth

R

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
[TABLE="width: 320"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]PN1[/TD]
[TD]description1[/TD]
[TD]PN2[/TD]
[TD="colspan: 2"]description2[/TD]
[/TR]
[TR]
[TD]UE37[/TD]
[TD]screw[/TD]
[TD]DY42[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]MT75[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]YN66[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PO87[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]UV75[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DY42[/TD]
[TD]screw[/TD]
[TD]UE37[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BP13[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PW58[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YN66[/TD]
[TD]screw[/TD]
[TD]YU44[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PO87[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]UV75[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UT14[/TD]
[TD]screw[/TD]
[TD]XL43[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]YD50[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]YN66[/TD]
[TD]screw[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KM91[/TD]
[TD]nut[/TD]
[TD]RM13[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]GJ29[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BK96[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]CZ89[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]EO41[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CQ81[/TD]
[TD]nut[/TD]
[TD]VH75[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]TT67[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]KM91[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KI49[/TD]
[TD]bolt[/TD]
[TD]PN10[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]HW91[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]JL63[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TH20[/TD]
[TD]bolt[/TD]
[TD]PR55[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PN10[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ZT51[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]YE57[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BY10[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]JL63[/TD]
[TD]bolt[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





Data expected after cleaning
[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]PN1[/TD]
[TD="width: 64"]description1[/TD]
[TD="width: 64"]PN2[/TD]
[TD="width: 64"]description2[/TD]
[TD="width: 64"]Unique product line ID[/TD]
[/TR]
[TR]
[TD]UE37[/TD]
[TD]screw[/TD]
[TD]DY42[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]MT75[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]YN66[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PO87[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]UV75[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD]DY42[/TD]
[TD]screw[/TD]
[TD]UE37[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BP13[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PW58[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD]YN66[/TD]
[TD]screw[/TD]
[TD]YU44[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PO87[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]UV75[/TD]
[TD]screw[/TD]
[TD]S001[/TD]
[/TR]
[TR]
[TD]AX32[/TD]
[TD]screw[/TD]
[TD]ZA55[/TD]
[TD]screw[/TD]
[TD]S002[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]XD40[/TD]
[TD]screw[/TD]
[TD]S002[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]AA23[/TD]
[TD]screw[/TD]
[TD]S002[/TD]
[/TR]
[TR]
[TD]KM91[/TD]
[TD]nut[/TD]
[TD]RM13[/TD]
[TD]nut[/TD]
[TD]N001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]GJ29[/TD]
[TD]nut[/TD]
[TD]N001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BK96[/TD]
[TD]nut[/TD]
[TD]N001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]CZ89[/TD]
[TD]nut[/TD]
[TD]N001[/TD]
[/TR]
[TR]
[TD]CQ81[/TD]
[TD]nut[/TD]
[TD]VH75[/TD]
[TD]nut[/TD]
[TD]N001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]TT67[/TD]
[TD]nut[/TD]
[TD]N001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]KM91[/TD]
[TD]nut[/TD]
[TD]N001[/TD]
[/TR]
[TR]
[TD]EO41[/TD]
[TD]nut[/TD]
[TD]OE23[/TD]
[TD]nut[/TD]
[TD]N002[/TD]
[/TR]
[TR]
[TD]KI49[/TD]
[TD]bolt[/TD]
[TD]PN10[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]HW91[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]JL63[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
[TR]
[TD]TH20[/TD]
[TD]bolt[/TD]
[TD]PR55[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PN10[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ZT51[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]YE57[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BY10[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]JL63[/TD]
[TD]bolt[/TD]
[TD]B001[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Hi Bhos123,

PN1 and PN2 are not primary keys. The aim is to generate one primary key for the products that have a link that we can identify.
For example, we have A = B, B = C then A = C. Say for product A we have allocated a random unique primary key, for this example 1. Say that A, B and C are then allocated the primary key 1.

Ronan
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi everyone,

Anyone creative to help me solving this question?
I have exhausted all my skills there...

Thank you for you help!

ronan
 
Upvote 0
Hi All,

it would be great if someone had an idea on this topic. I am really lost here...

Thank you for your help!

Ronan
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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