Part Number Spaghetti Bowl (a REAL mess)

N

NitroRoo

New Member
Joined
Feb 11, 2016
Messages
16
Hi Everyone,

I've received a TON of useful knowledge on this site over the past year, so THANK YOU to everyone who contributes. I finally joined because I have quite the puzzle to solve.

I am working on a spreadsheet with roughly 48k rows, each representing a unique product. Our system is setup such that Products Numbers are unique, and are what we sell. Products are nested under PARTS in our system, which are what we buy. Then we have VendorPartNumber which is the number used by our vendor when we purchase said part. So we have Part, Product, and Vendor Part Numbers in our system.

Now, major mistake number one that was made was not using our own part numbering system from the start. We have part and product numbers that were assigned using manufacturer's OEM numbers - some of which are very basic 4 digit numerical part numbers - so very high chances for duplicates from other OEMs (plus leading zeroes and a slew of other bad things). Second big mistake is creating multiple part numbers for the same part - which is what I'm trying to fix right now. Eventually I'm going to get this company switched over to using their own proprietary part number system - this is a mess.

So here's what I need. I have exported roughly 26k parts from 1 vendor. Each product gets its own row in the csv, which means the part number and vendor number columns repeat for each product under those parts. You can see below that vendor number S-B610 is setup correctly below. Each instance of that vendor number is under part number 00701. The last two rows show the problem, S-7419 is assigned to two different part numbers. Those two products and vendor numbers should be under 1 part, not two. There are thousands like this. What's the best way to identify that VendorPartNumber is tied to only 1 unique PartNumber?


<colgroup span="2" width="270"></colgroup> <colgroup width="316"></colgroup> <tbody>
[TD="align: left"]PartNumber[/TD]
[TD="align: left"]ProductNumber[/TD]
[TD="align: left"]VendorPartNumber[/TD]

[TD="align: left"]41300[/TD]
[TD="align: left"]41300[/TD]
[TD="align: left"]S-8444[/TD]

[TD="align: left"]0000704[/TD]
[TD="align: left"]0000704[/TD]
[TD="align: left"]S-8641[/TD]

[TD="align: left"]00701[/TD]
[TD="align: left"]00701[/TD]
[TD="align: left"]S-B610[/TD]

[TD="align: left"]00701[/TD]
[TD="align: left"]29428X[/TD]
[TD="align: left"]S-B610[/TD]

[TD="align: left"]00701[/TD]
[TD="align: left"]10089 Neapco[/TD]
[TD="align: left"]S-B610[/TD]

[TD="align: left"]00701[/TD]
[TD="align: left"]A490K[/TD]
[TD="align: left"]S-B610[/TD]

[TD="align: left"]00701[/TD]
[TD="align: left"]KT116[/TD]
[TD="align: left"]S-B610[/TD]

[TD="align: left"]82408[/TD]
[TD="align: left"]82408[/TD]
[TD="align: left"]S-D817[/TD]

[TD="align: left"]110243[/TD]
[TD="align: left"]110243[/TD]
[TD="align: left"]S-7931[/TD]

[TD="align: left"]Blind Ship[/TD]
[TD="align: left"]Blind Ship[/TD]
[TD="align: left"]S-18124[/TD]

[TD="align: left"]FUL16758[/TD]
[TD="align: left"]FUL16758[/TD]
[TD="align: left"]S-7419[/TD]

[TD="align: left"]K-1650[/TD]
[TD="align: left"]K-1650[/TD]
[TD="align: left"]S-7419[/TD]

</tbody>
 
Sort by date Sort by votes
You could sort by vendor p/n, then

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][/tr][tr][td]
1​
[/td][td="bgcolor:#F3F3F3"]
PartNumber​
[/td][td="bgcolor:#F3F3F3"]
ProductNumber​
[/td][td="bgcolor:#F3F3F3"]
VendorPartNumber​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][/tr]
[tr][td]
2​
[/td][td]Blind Ship[/td][td]Blind Ship[/td][td]S-18124[/td][td]OK[/td][td]D2: =IF((C2=C1)*(B2<>B1), "Oops", "OK")[/td][/tr]
[tr][td]
3​
[/td][td]FUL16758[/td][td]FUL16758[/td][td]S-7419[/td][td]OK[/td][td][/td][/tr]
[tr][td]
4​
[/td][td]K-1650[/td][td]K-1650[/td][td]S-7419[/td][td]Oops[/td][td][/td][/tr]
[tr][td]
5​
[/td][td]
110243​
[/td][td]
110243​
[/td][td]S-7931[/td][td]OK[/td][td][/td][/tr]
[tr][td]
6​
[/td][td]
41300​
[/td][td]
41300​
[/td][td]S-8444[/td][td]OK[/td][td][/td][/tr]
[tr][td]
7​
[/td][td]
704​
[/td][td]
704​
[/td][td]S-8641[/td][td]OK[/td][td][/td][/tr]
[tr][td]
8​
[/td][td]
701​
[/td][td]
701​
[/td][td]S-B610[/td][td]OK[/td][td][/td][/tr]
[tr][td]
9​
[/td][td]
701​
[/td][td]29428X[/td][td]S-B610[/td][td]Oops[/td][td][/td][/tr]
[tr][td]
10​
[/td][td]
701​
[/td][td]10089 Neapco[/td][td]S-B610[/td][td]Oops[/td][td][/td][/tr]
[tr][td]
11​
[/td][td]
701​
[/td][td]A490K[/td][td]S-B610[/td][td]Oops[/td][td][/td][/tr]
[tr][td]
12​
[/td][td]
701​
[/td][td]KT116[/td][td]S-B610[/td][td]Oops[/td][td][/td][/tr]
[tr][td]
13​
[/td][td]
82408​
[/td][td]
82408​
[/td][td]S-D817[/td][td]OK[/td][td][/td][/tr]
[/table]
 
Upvote 0
That's close. I changed (b2<>b1) to (a2<>a1) because A in my example is the part number field we are concerned with (S-B610 should have been "OK" in this case).

This somewhat works - but runs into problems such as the example below, where S-10180 has some part numbers (col A) that match and some that don't - they all need to be 1 number:


<tbody>
[TD="align: left"]6.5-4-4451-1[/TD]
[TD="align: left"]6.5444511[/TD]
[TD="align: left"]S-10180[/TD]
[TD="align: left"]OK[/TD]

[TD="align: left"]6.5-4-4461-1X[/TD]
[TD="align: left"]6.5444611X[/TD]
[TD="align: left"]S-10180[/TD]
[TD="align: left"]oops[/TD]

[TD="align: left"]SP 6.5-4-44611X[/TD]
[TD="align: left"]SP6.5444611X[/TD]
[TD="align: left"]S-10180[/TD]
[TD="align: left"]oops[/TD]

[TD="align: left"]SP 6.5-4-44611X[/TD]
[TD="align: left"]SP 6.5-4-44611X[/TD]
[TD="align: left"]S-10180[/TD]
[TD="align: left"]OK[/TD]

[TD="align: left"]18N4-4571X[/TD]
[TD="align: left"]18N44571X[/TD]
[TD="align: left"]S-10180[/TD]
[TD="align: left"]oops[/TD]

[TD="align: left"]18N4-4571X[/TD]
[TD="align: left"]18N4-4571X[/TD]
[TD="align: left"]S-10180[/TD]
[TD="align: left"]OK[/TD]

[TD="align: left"]SP 6.5-4-4461-1X[/TD]
[TD="align: left"]SP 6.5-4-4461-1X[/TD]
[TD="align: left"]S-10180[/TD]
[TD="align: left"]oops

[/TD]

</tbody>

If I could somehow extract or select each block of cells (or entire rows) where this happens, I can make some progress. As it sits right now I can filter out all the "OK" rows and work on the oops rows - but as you can see that wouldn't fully solve my problem.
 
Upvote 0
Retracted prior code it didn't help the problem as I reread what you're trying to accomplish, Sorry.
 
Last edited:
Upvote 0
I'm wondering if this would help. Or could be adapted to help.

Code: 
Sub RowReplace()

Dim ConfirmCol As Range
Dim Cell As Range
Set ConfirmCol = Range("D:D")

For Each Cell In ConfirmCol
    If Cell.Value = "oops" Then
        X = Cell.Row
        Y = Cell.Column
        Cells(X, Y - 2).Value = Cells(X, Y - 3).Value
    End If
    Next Cell

End Sub

It would sort through all the oops entries in column D. If it find an oops it will replace Column B with what is in Column A.
 
Upvote 0
Meggesto said:
I'm wondering if this would help. Or could be adapted to help.

Code: 
Sub RowReplace()

Dim ConfirmCol As Range
Dim Cell As Range
Set ConfirmCol = Range("D:D")

For Each Cell In ConfirmCol
    If Cell.Value = "oops" Then
        X = Cell.Row
        Y = Cell.Column
        Cells(X, Y - 2).Value = Cells(X, Y - 3).Value
    End If
    Next Cell

End Sub

It would sort through all the oops entries in column D. If it find an oops it will replace Column B with what is in Column A.
Click to expand...

Thank you for the idea. However in the example I shared in Post #3 I would miss rows 1, 4, and 6 using the oops formula. Out of ~26k rows I had close to 10k "oops" results - and that's not all of them because of the fact that some vendor numbers may repeat 10 times with two or three pairs of part numbers that trick the oops formula into thinking that row is ok.
 
Upvote 0
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][/tr][tr][td]
1​
[/td][td="bgcolor:#F3F3F3"]
PartNumber​
[/td][td="bgcolor:#F3F3F3"]
ProductNumber​
[/td][td="bgcolor:#F3F3F3"]
VendorPartNumber​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][td="bgcolor:#F3F3F3"]
​
[/td][/tr]
[tr][td]
2​
[/td][td]Blind Ship[/td][td]irrelevant[/td][td]S-18124[/td][td][/td][td]D2: =IF(COUNTIFS($C$2:$C$13, C2, $A$2:$A$13, "<>" & A2), "oops", "")[/td][/tr]
[tr][td]
3​
[/td][td]FUL16758[/td][td]irrelevant[/td][td]S-7419[/td][td]oops[/td][td][/td][/tr]
[tr][td]
4​
[/td][td]K-1650[/td][td]irrelevant[/td][td]S-7419[/td][td]oops[/td][td][/td][/tr]
[tr][td]
5​
[/td][td]
110243​
[/td][td]irrelevant[/td][td]S-7931[/td][td][/td][td][/td][/tr]
[tr][td]
6​
[/td][td]
41300​
[/td][td]irrelevant[/td][td]S-8444[/td][td][/td][td][/td][/tr]
[tr][td]
7​
[/td][td]
704​
[/td][td]irrelevant[/td][td]S-8641[/td][td][/td][td][/td][/tr]
[tr][td]
8​
[/td][td]
701​
[/td][td]irrelevant[/td][td]S-B610[/td][td][/td][td][/td][/tr]
[tr][td]
9​
[/td][td]
701​
[/td][td]irrelevant[/td][td]S-B610[/td][td][/td][td][/td][/tr]
[tr][td]
10​
[/td][td]
701​
[/td][td]irrelevant[/td][td]S-B610[/td][td][/td][td][/td][/tr]
[tr][td]
11​
[/td][td]
701​
[/td][td]irrelevant[/td][td]S-B610[/td][td][/td][td][/td][/tr]
[tr][td]
12​
[/td][td]
701​
[/td][td]irrelevant[/td][td]S-B610[/td][td][/td][td][/td][/tr]
[tr][td]
13​
[/td][td]
82408​
[/td][td]irrelevant[/td][td]S-D817[/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
shg said:
[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD="bgcolor: #F3F3F3"]
PartNumber​
[/TD]
[TD="bgcolor: #F3F3F3"]
ProductNumber​
[/TD]
[TD="bgcolor: #F3F3F3"]
VendorPartNumber​
[/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]Blind Ship[/TD]
[TD]irrelevant[/TD]
[TD]S-18124[/TD]
[TD][/TD]
[TD]D2: =IF(COUNTIFS($C$2:$C$13, C2, $A$2:$A$13, "<>" & A2), "oops", "")[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]FUL16758[/TD]
[TD]irrelevant[/TD]
[TD]S-7419[/TD]
[TD]oops[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]K-1650[/TD]
[TD]irrelevant[/TD]
[TD]S-7419[/TD]
[TD]oops[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
110243​
[/TD]
[TD]irrelevant[/TD]
[TD]S-7931[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]
41300​
[/TD]
[TD]irrelevant[/TD]
[TD]S-8444[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]
704​
[/TD]
[TD]irrelevant[/TD]
[TD]S-8641[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]
701​
[/TD]
[TD]irrelevant[/TD]
[TD]S-B610[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]
701​
[/TD]
[TD]irrelevant[/TD]
[TD]S-B610[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]
701​
[/TD]
[TD]irrelevant[/TD]
[TD]S-B610[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]
701​
[/TD]
[TD]irrelevant[/TD]
[TD]S-B610[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD]
701​
[/TD]
[TD]irrelevant[/TD]
[TD]S-B610[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD]
82408​
[/TD]
[TD]irrelevant[/TD]
[TD]S-D817[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

That worked! Thank you SO much. :beerchug:
 
Upvote 0
You're welcome.

Suggestion for next time: Don't put irrelevant data in your question (column B in this case). It just confuses the issue.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Returning Multiple Values Through A Vlookup - Formula or VBA
Replies
0
Views
2K
liamlarmour
L

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