Finding duplicates in excel

  • Thread starter Thread starter Rhino
  • Start date Start date
R

Rhino

Hi guys, I have a big worksheet with a ton of columns, what I need to do is
find all the numbers from column B that are in column A and create a list of
cells from column B that are unique and not in column A. The sheet looks
like this but goes on for 5K rows. If you look at the 2 columns the first
number in column B (11971268365) is also in column A and I don't want that
number. I do want the 2nd and 3rd number (etc). Can you guys help me do
this? I can't seem to figure it out on my own. Please feel free to e-mail
me as well (e-mail address removed)

Thanks guys

A B
CU83201 AcctNum
6100038 11971268365
2545179031 13519408214
11971268365 14120788327
 
Sub findcolBinColA()
For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
Set mf = Columns(1).Find(What:=Cells(i, 2), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If mf Is Nothing Then
Cells(Cells(Rows.Count, 3).End(xlUp).Row + 1, 3) = Cells(i, 2)
End If
Next i
End Sub
 
Rhino try this:
In C2 enter formula:
=IF(B2="","",IF(ISNUMBER(MATCH(B2,A:A,0)),"",ROW()))
In D2 enter formula:
=IF(ROWS($1:1)>COUNT(C:C),"",INDEX(B:B,SMALL(C:C,ROWS($1:1))))
In E2 enter formula:
=IF(D2="","",IF(AND(COUNTIF(A:A,D2),COUNTIF(B:B,D2)),"In A &
B",IF(AND(COUNTIF(A:A,D2),NOT(COUNTIF(B:B,D2))),"In A not
B",IF(AND(NOT(COUNTIF(A:A,D2)),COUNTIF(B:B,D2)),"In B not A","Not in A nor
B"))))
Copy or drag the three formulae down to the bottom of your data. Hide column C
You are now left with column D containing your unique list of numbers and
column E will tell you which column the unique number appears.
Hope this helps
 
Hmm, I tried to cut and paste the formula in but I kept getting a pop up that
the formula contained an error.
 
Back
Top