Compare two collums with nine numbers.

[blake20]

fellow members I have a slight problem with a comparison of two collum's.
collum A and B have 9 numbers (in a cell) and about 40.000 rows in A and about 34.000 in B.

Now a have to compare those collums and those numers.
Do all the numbers in A also stand in B?
The numbers that are not in B have to go to an new sheet named "Rest".

I have never don such a search can you help me?

Privacy rules forbid me to make the workbook public.

[cytop]

Privacy rules forbid me to make the workbook public

Then post an anonymised sample, it will help to clarify what you mean by "9 numbers (in a cell)". For example - 123456789...? 1,4,2,6,45,76,89,12,34 ...??

[sanram]

You can do that with the help of a helper column easily. See the attachment. Here Column C of Sheet named "Numbers" is helper column. In the cell C2 I have used this formula to find that which number of column A doesn't lie in column B and copy down :

=IF(COUNTIF($B$2:$B$9,$A2)>0,"",ROW())

Then I have used below formula in the cell A2 of Sheet named "Rest" to draw those numbers from the sheet named "Numbers" and copy down :

=IFERROR(INDEX(Numbers!$A$2:$A$9,SMALL(Numbers!$C$2:$C$9,COUNTA($A$1:$A1))-1),"")

Hope that will help.

[JOHN H. DAVIS]

Does this help?

Sub blake20()
Dim i As Long, x As Worksheet, y, z As Range
Set x = Sheets("Rest")
ReDim y(2 To Range("A" & Rows.Count).End(3).row)
For i = LBound(y) To UBound(y)
    Set z = Columns(2).Find(Cells(i, "A"), LookIn:=xlValues, Lookat:=xlWhole)
        If z Is Nothing Then
            Cells(i, "A").Copy x.Cells(Rows.Count, "A").End(3)(2)
        End If
    Set z = Nothing
Next i
End Sub

[blake20]

Sorry for the late reaction but i have been sick.
Everybody thanks for their reactions.
Especialy John H Davis. It worked like a charm.

thx.

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. Please mark this thread as SOLVED.

[sanram]

Glad to hear that you have found the solution.