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>
 

Ronan_1

New Member
Joined
Feb 16, 2017
Messages
10
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
 

Ronan_1

New Member
Joined
Feb 16, 2017
Messages
10
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
 

bhos123

Well-known Member
Joined
May 2, 2016
Messages
876
Now you have classified products into S001, S002 , N001 , B001..etc. what exactly you want to do next?
 

Ronan_1

New Member
Joined
Feb 16, 2017
Messages
10
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
 

Ronan_1

New Member
Joined
Feb 16, 2017
Messages
10
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
 

bhos123

Well-known Member
Joined
May 2, 2016
Messages
876
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:

Ronan_1

New Member
Joined
Feb 16, 2017
Messages
10
Thank you for your answer. Is it possible to have a unique primary key that is automatically generated?
 

bhos123

Well-known Member
Joined
May 2, 2016
Messages
876
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.
 

Forum statistics

Threads
1,082,040
Messages
5,362,804
Members
400,693
Latest member
jenlj

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top