Matching up Duplicate Cross Reference Numbers

[NitroRoo]

I have a sheet with ~14k cross reference numbers. Column A is the Vendor number, and Column B is the possible OEM number for that part. I need to identify when the parts in Column B occur next to more than 1 number in column A. In the example below I have highlighted parts that have duplicate values in Column B. You can see that the first two highlighted duplicates do not have their duplicate values next to vendornum 1011015 (they occur further down the list next to 1017003). However the two middle duplicate values are both next to 1012006, so those are ok (for now, next step will be to pull those as it's a separate issue).

The goal is to identify oemnum such as 301322 and 3322, and ultimately pull those entire rows into another sheet and remove them from the original.

[TABLE="align: left"]
<tbody>[TR]
[TD="******* 19, align: right"]1[/TD]
[TD="******* 81, align: left"]vendornum[/TD]
[TD="******* 123, align: left"]oemnum[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]2[/TD]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 123, align: left"]ZGG11028[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]3[/TD]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 123, align: left"]C7AZ-19805-B[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]4[/TD]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 123, align: left"]35501[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]5[/TD]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 123, bgcolor: #FFC7CE, align: left"]301322[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]6[/TD]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 123, bgcolor: #FFC7CE, align: left"]3322[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]7[/TD]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 123, align: left"]01-2680[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]8[/TD]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 123, align: left"]BM-0393[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]9[/TD]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 123, align: left"]1011015[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]10[/TD]
[TD="******* 81, align: left"]1012006[/TD]
[TD="******* 123, bgcolor: #FFC7CE, align: left"]BOA-80-415-00-789[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]11[/TD]
[TD="******* 81, align: left"]1012006[/TD]
[TD="******* 123, align: left"]2898-091-028[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]12[/TD]
[TD="******* 81, align: left"]1012006[/TD]
[TD="******* 123, align: left"]301317[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]13[/TD]
[TD="******* 81, align: left"]1012006[/TD]
[TD="******* 123, bgcolor: #FFC7CE, align: left"]BOA-80-415-00-789[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]14[/TD]
[TD="******* 81, align: left"]1012006[/TD]
[TD="******* 123, align: left"]ZGG701024[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]15[/TD]
[TD="******* 81, align: left"]1012006[/TD]
[TD="******* 123, align: left"]3944[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]16[/TD]
[TD="******* 81, align: left"]1012006[/TD]
[TD="******* 123, align: left"]01-0609[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]17[/TD]
[TD="******* 81, align: left"]1012006[/TD]
[TD="******* 123, align: left"]274379[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]18[/TD]
[TD="******* 81, align: left"]1017003[/TD]
[TD="******* 123, align: left"]35546[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]19[/TD]
[TD="******* 81, align: left"]1017003[/TD]
[TD="******* 123, bgcolor: #FFC7CE, align: left"]301322[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]20[/TD]
[TD="******* 81, align: left"]1017003[/TD]
[TD="******* 123, align: left"]BSM-301082[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]21[/TD]
[TD="******* 81, align: left"]1017003[/TD]
[TD="******* 123, align: left"]1691705C91[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]22[/TD]
[TD="******* 81, align: left"]1017003[/TD]
[TD="******* 123, bgcolor: #FFC7CE, align: left"]3322[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]23[/TD]
[TD="******* 81, align: left"]1017003[/TD]
[TD="******* 123, align: left"]01-2608,-01-2624[/TD]
[/TR]
[TR]
[TD="******* 19, align: right"]24[/TD]
[TD="******* 81, align: left"]1017003[/TD]
[TD="******* 123, align: left"]274318[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

The advanced filter can hide duplicates, but in this case I'd put a countif formula in the next column, then you can filter oemnum counts>1, and with F5-special-visible copy and paste the unique rows or change the formula to =1 to move the duplicates.

 

[NitroRoo]

Thanks for the reply. I've been trying to accomplish this with a countifs formula. I can't simply filter duplicates because the data on rows 10 and 13 would be affected.

=IF(COUNTIFS($A$2:$A$24,A10,$B$2:$B$24,B10)>1,"YES","NO")

gives me a yes for 10 and 13 but I'm trying to figure out a formula that identifies the all of the duplicates that occur next to more than 1 oemnum.

 

[sheetspread]

This?

Excel 2010
	A	B	C
1	vendornum	oemnum
2	1011015	ZGG11028	0
3	1011015	C7AZ-19805-B	0
4	1011015	35501	0
5	1011015	301322	1
6	1011015	3322	1
7	1011015	Jan-80	0
8	1011015	BM-0393	0
9	1011015	1011015	0
10	1012006	BOA-80-415-00-789	0
11	1012006	2898-091-028	0
12	1012006	301317	0
13	1012006	BOA-80-415-00-789	0
14	1012006	ZGG701024	0
15	1012006	3944	0
16	1012006	01-0609	0
17	1012006	274379	0
18	1017003	35546	0
19	1017003	301322	1
20	1017003	BSM-301082	0
21	1017003	1691705C91	0
22	1017003	3322	1
23	1017003	01-2608,-01-2624	0
24	1017003	274318	0
Sheet4 (2)

Cell Formulas
Range	Formula
C2	=COUNTIFS($B$2:$B$24,B2,$A$2:$A$24,"<>"&A2)

 

[NitroRoo]

Yes, I've tried that exact formula, but when I put it in my full sheet this is what I get:

Duplicates highlighted in red should all give a 1, everything else in this range does not even have a duplicate. This is what is throwing me off... Could numbers stored as text cause this behavior?

Here's the formula (result in 3rd column):
=COUNTIFS($B$2:$B$14052,B40,$A$2:$A$14052,"<>"&A40)

[TABLE="align: left"]
<tbody>[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, align: left"]E-803012[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, align: left"]HV86524[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, align: left"]900314[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, align: left"]ZGG11028[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, align: left"]C7AZ-19805-B[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, align: left"]35501[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, bgcolor: #FFC7CE, align: left"]301322[/TD]
[TD="******* 62, align: right"]3[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, bgcolor: #FFC7CE, align: left"]3322[/TD]
[TD="******* 62, align: right"]3[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, align: left"]01-2680[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, align: left"]WW29050Q[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, align: left"]208905[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, align: left"]274418[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, align: left"]HB1155[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, align: left"]26-13265[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, bgcolor: #FFC7CE, align: left"]73R4322[/TD]
[TD="******* 62, align: right"]3[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, bgcolor: #FFC7CE, align: left"]RD-5-10068-0P[/TD]
[TD="******* 62, align: right"]2[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, align: left"]BM-0393[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 198, align: left"]1011015[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

Identical numbers should be counted the same even if they're text (test by changing the formatting, preceding with an apostrophe or ending with &""). Does the A40 you have coincide with the cell/row it's supposed to be checking?

They shouldn't return a 1, you're counting how many different oemnums correspond to a single vendornum

 

[NitroRoo]

Yes, row 40 is 301322 on the main sheet.

I just tried pasting in the exact formula you posted on my example sheet I'm using, and it still isn't behaving. This is what I get:

Formula: =COUNTIFS($B$2:$B$24,B2,$A$2:$A$24,"<>"&A2)

[TABLE="align: left"]
<tbody>[TR]
[TD="******* 81, align: left"]vendornum[/TD]
[TD="******* 123, align: left"]oemnum[/TD]
[TD="******* 62, align: left"] [/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 123, align: left"]ZGG11028[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 123, align: left"]C7AZ-19805-B[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 123, align: left"]35501[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 123, bgcolor: #FFC7CE, align: left"]301322[/TD]
[TD="******* 62, align: right"]2[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 123, bgcolor: #FFC7CE, align: left"]3322[/TD]
[TD="******* 62, align: right"]2[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 123, align: left"]01-2680[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 123, align: left"]BM-0393[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1011015[/TD]
[TD="******* 123, align: left"]1011015[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1012006[/TD]
[TD="******* 123, bgcolor: #FFC7CE, align: left"]BOA-80-415-00-789[/TD]
[TD="******* 62, align: right"]2[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1012006[/TD]
[TD="******* 123, align: left"]2898-091-028[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1012006[/TD]
[TD="******* 123, align: left"]301317[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1012006[/TD]
[TD="******* 123, bgcolor: #FFC7CE, align: left"]BOA-80-415-00-789[/TD]
[TD="******* 62, align: right"]2[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1012006[/TD]
[TD="******* 123, align: left"]ZGG701024[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1012006[/TD]
[TD="******* 123, align: left"]3944[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1012006[/TD]
[TD="******* 123, align: left"]01-0609[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1012006[/TD]
[TD="******* 123, align: left"]274379[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1017003[/TD]
[TD="******* 123, align: left"]35546[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1017003[/TD]
[TD="******* 123, bgcolor: #FFC7CE, align: left"]301322[/TD]
[TD="******* 62, align: right"]2[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1017003[/TD]
[TD="******* 123, align: left"]BSM-301082[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1017003[/TD]
[TD="******* 123, align: left"]1691705C91[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1017003[/TD]
[TD="******* 123, bgcolor: #FFC7CE, align: left"]3322[/TD]
[TD="******* 62, align: right"]2[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1017003[/TD]
[TD="******* 123, align: left"]01-2608,-01-2624[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[TD="******* 81, align: left"]1017003[/TD]
[TD="******* 123, align: left"]274318[/TD]
[TD="******* 62, align: right"]1[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

 

[MickG]

This code will place relevant rows on sheet 2 and delete them from sheet1.

[COLOR="Navy"]Sub[/COLOR] MG01Sep55
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng        [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic         [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Q           [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] k           [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] p           [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
 [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
        
        [COLOR="Navy"]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, -1).Value) [COLOR="Navy"]Then[/COLOR]
                Dic(Dn.Value).Add (Dn.Offset(, -1).Value), Dn
        [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value).Item(Dn.Offset(, -1).Value) = _
                Union(Dic(Dn.Value).Item(Dn.Offset(, -1).Value), Dn)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
   
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
    [COLOR="Navy"]If[/COLOR] Dic(k).Count > 1 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(k)
            [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dic(k).Item(p) Else _
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dic(k).Item(p))
        [COLOR="Navy"]Next[/COLOR] p
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] nRng
        c = c + 1
        [COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
            .Cells(c, 1) = Dn.Offset(, -1).Value
            .Cells(c, 2) = Dn.Value
        [COLOR="Navy"]End[/COLOR] With
    [COLOR="Navy"]Next[/COLOR] Dn
nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[sheetspread]

(Forget what I wrote before if you saw it)

That is odd behavior, is your calc set to manual?

 

[NitroRoo]

Edit: Calculation is set to automatic. This was a blank workbook that I pasted values only into to work on. The actual spreadsheet I ultimately need to fix has many, many columns. I'm on Excel 2013.

I will see what I can do with MickG's code that's diving in pretty deep for me but hey I don't have anything to lose! I really appreciate the help.