Part Number Spaghetti Bowl (a REAL mess)

[NitroRoo]

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?

PartNumber	ProductNumber	VendorPartNumber
41300	41300	S-8444
0000704	0000704	S-8641
00701	00701	S-B610
00701	29428X	S-B610
00701	10089 Neapco	S-B610
00701	A490K	S-B610
00701	KT116	S-B610
82408	82408	S-D817
110243	110243	S-7931
Blind Ship	Blind Ship	S-18124
FUL16758	FUL16758	S-7419
K-1650	K-1650	S-7419

<colgroup span="2" width="270"></colgroup> <colgroup width="316"></colgroup> <tbody>
</tbody>

 

[shg]

You could sort by vendor p/n, then

Row\Col	A​	B​	C​	D​	E​
1​	PartNumber​	ProductNumber​	VendorPartNumber​	​	​
2​	Blind Ship	Blind Ship	S-18124	OK	D2: =IF((C2=C1)*(B2<>B1), "Oops", "OK")
3​	FUL16758	FUL16758	S-7419	OK
4​	K-1650	K-1650	S-7419	Oops
5​	110243​	110243​	S-7931	OK
6​	41300​	41300​	S-8444	OK
7​	704​	704​	S-8641	OK
8​	701​	701​	S-B610	OK
9​	701​	29428X	S-B610	Oops
10​	701​	10089 Neapco	S-B610	Oops
11​	701​	A490K	S-B610	Oops
12​	701​	KT116	S-B610	Oops
13​	82408​	82408​	S-D817	OK

 

[NitroRoo]

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:

6.5-4-4451-1	6.5444511	S-10180	OK
6.5-4-4461-1X	6.5444611X	S-10180	oops
SP 6.5-4-44611X	SP6.5444611X	S-10180	oops
SP 6.5-4-44611X	SP 6.5-4-44611X	S-10180	OK
18N4-4571X	18N44571X	S-10180	oops
18N4-4571X	18N4-4571X	S-10180	OK
SP 6.5-4-4461-1X	SP 6.5-4-4461-1X	S-10180	oops

<tbody>
</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.

 

[Meggesto]

Retracted prior code it didn't help the problem as I reread what you're trying to accomplish, Sorry.

 

[Meggesto]

I'm wondering if this would help. Or could be adapted to help.

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.

 

[NitroRoo]

I'm wondering if this would help. Or could be adapted to help.

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.

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.

 

[shg]

Row\Col	A​	B​	C​	D​	E​
1​	PartNumber​	ProductNumber​	VendorPartNumber​	​	​
2​	Blind Ship	irrelevant	S-18124		D2: =IF(COUNTIFS($C$2:$C$13, C2, $A$2:$A$13, "<>" & A2), "oops", "")
3​	FUL16758	irrelevant	S-7419	oops
4​	K-1650	irrelevant	S-7419	oops
5​	110243​	irrelevant	S-7931
6​	41300​	irrelevant	S-8444
7​	704​	irrelevant	S-8641
8​	701​	irrelevant	S-B610
9​	701​	irrelevant	S-B610
10​	701​	irrelevant	S-B610
11​	701​	irrelevant	S-B610
12​	701​	irrelevant	S-B610
13​	82408​	irrelevant	S-D817

 

[NitroRoo]

Row\Col	A​	B​	C​	D​	E​
1​	PartNumber​	ProductNumber​	VendorPartNumber​
2​	Blind Ship	irrelevant	S-18124		D2: =IF(COUNTIFS($C$2:$C$13, C2, $A$2:$A$13, "<>" & A2), "oops", "")
3​	FUL16758	irrelevant	S-7419	oops
4​	K-1650	irrelevant	S-7419	oops
5​	110243​	irrelevant	S-7931
6​	41300​	irrelevant	S-8444
7​	704​	irrelevant	S-8641
8​	701​	irrelevant	S-B610
9​	701​	irrelevant	S-B610
10​	701​	irrelevant	S-B610
11​	701​	irrelevant	S-B610
12​	701​	irrelevant	S-B610
13​	82408​	irrelevant	S-D817

<tbody>
</tbody>

That worked! Thank you SO much.

 

[shg]

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.