Matching up Duplicate Cross Reference Numbers

N

NitroRoo

New Member
Joined
Feb 11, 2016
Messages
16
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]
 
Sort by date Sort by votes
I just tested your other data (had the wrong one before)


Excel 2010
ABCDEFG
1vendornumoemnumvendornumoemnum
21011015ZGG1102801011015ZGG110280
31011015C7AZ-19805-B01011015C7AZ-19805-B0
410110153550101011015355010
51011015301322110110153013221
6101101533221101101533221
71011015Jan-8001011015Jan-800
81011015BM-039301011015BM-03930
9101101510110150101101510110150
101012006BOA-80-415-00-78901012006BOA-80-415-00-7890
1110120062898-091-028010120062898-091-0280
121012006301317010120063013170
131012006BOA-80-415-00-78901012006BOA-80-415-00-7890
141012006ZGG70102401012006ZGG7010240
15101200639440101200639440
16101200601-06090101200601-06090
171012006274379010120062743790
1810170033554601017003355460
191017003301322110170033013221
201017003BSM-30108201017003BSM-3010820
2110170031691705C91010170031691705C910
22101700333221101700333221
23101700301-2608,-01-26240101700301-2608,-01-26240
241017003274318010170032743180
Sheet4 (2)
Cell Formulas
RangeFormula
C2=COUNTIFS($B$2:$B$24,B2,$A$2:$A$24,"<>"&A2)
G2=COUNTIFS($F$2:$F$24,F2,$E$2:$E$24,"<>"&E2)
 
Last edited:
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
I'm still confused about how the exact same formula worked in post #4 but doesn't work on my sheet.

At any rate, I ran MickG's code and it worked! Brilliant stuff MickG thank you SO much!
 
Upvote 0
NitroRoo said:
I'm still confused about how the exact same formula worked in post #4 but doesn't work on my sheet.

At any rate, I ran MickG's code and it worked! Brilliant stuff MickG thank you SO much!
Click to expand...

Glad it worked. Let me know if you find out why the formula results were different, or feel free to post another sample or send one to me.
 
Upvote 0
MickG said:
You're welcome
Click to expand...

My main spreadsheet goes to column DA. Could this code be modified to pull the entire row?

Also, the data in sheet 2 looks like it loses its formatting. Example: 01-2021 gets converted to 1/1/2021 (displayed as Jan-21). Can we prevent that?
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG01Sep48
[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")
            .Columns(2).NumberFormat = "@"
            .Cells(c, 1).Resize(, 106) = Dn.Offset(, -1).Resize(, 106).Value
        [COLOR="Navy"]End[/COLOR] With
    [COLOR="Navy"]Next[/COLOR] Dn
nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
That one produced a Run-time error '9': Subscript out of range. Tried with the full sheet as well as only column a and b.
 
Upvote 0
sheetspread said:
That could distort the formula results, 01-2021 is not the same as 44197, its date code.
Click to expand...

Not sure where 44197 came from... I just had cell with a value 01-2021 which excel then converts to a date. If you look at the 7th row in the original example and what you posted in reply #11 it got converted to Jan-80. Oh the fun excel has with these part numbers...

I've been trying all sorts of things to get the countifs function to work to no avail. Cells that I KNOW do not match anything else are still being counted.. it makes no sense.
 
Upvote 0
Yes, that's Jan 1, 2680 (284891 in date code). If you format the cells as text first you can prevent the automatic date conversion. Usually database imports (which you may have) are automatically formatted as text in Excel, but changing individual cells can revert them to numbers distorting the consistent sample.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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