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

Thanks guys

A B
CU83201 AcctNum
6100038 11971268365
2545179031 13519408214
11971268365 14120788327

See Chip Pearson's site for info and formulas.

http://www.cpearson.com/excel/ListFunctions.aspx


Gord Dibben MS Excel MVP

On Fri, 16 Oct 2009 13:16:01 -0700, Rhino
wrote:

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

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rhino" wrote in message
...
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

Thanks guys

A B
CU83201 AcctNum
6100038 11971268365
2545179031 13519408214
11971268365 14120788327

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,R OWS($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))),"I n A not
B",IF(AND(NOT(COUNTIF(A:A,D2)),COUNTIF(B:B,D2)),"I n 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



"Rhino" wrote:

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

Thanks guys

A B
CU83201 AcctNum
6100038 11971268365
2545179031 13519408214
11971268365 14120788327

Hmm, I tried to cut and paste the formula in but I kept getting a pop up that
the formula contained an error.

"Ron@Buy" wrote:

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,R OWS($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))),"I n A not
B",IF(AND(NOT(COUNTIF(A:A,D2)),COUNTIF(B:B,D2)),"I n 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



"Rhino" wrote:

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

Thanks guys

A B
CU83201 AcctNum
6100038 11971268365
2545179031 13519408214
11971268365 14120788327