I have two columns with 1000+ numbers in them. Column 1 is the correct
column. I want to compare columns 1 and 2 and move those in #2 that don't
match or appear in #1 to a third column. End result should be #' 1 and 2
match and a third column contains #'s that were in #2 but didn't match #1.
make any sense?

This formula will show the unique numbers in column B that are not found in column A; paste it in cell C2 and copy down.

=IF(COUNTIF($A:$A,B2)=0,B2,"")

One play which drives it out ..
Assume source data in cols A and B, in row 2 down
In C2: =IF(B2="","",IF(ISNUMBER(MATCH(B2,A:A,0)),"",ROW() ))
Leave C1 blank
In D2:
=IF(ROWS($1:1)COUNT(C:C),"",INDEX(B:B,SMALL(C:C,R OWS($1:1))))
Select C2:D2, copy down to cover the max expected extent of data in col B,
say down to D5000? Minmize/hide col C. Col D will return what you seek.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DRKENNE" wrote:
I have two columns with 1000+ numbers in them. Column 1 is the correct
column. I want to compare columns 1 and 2 and move those in #2 that don't
match or appear in #1 to a third column. End result should be #' 1 and 2
match and a third column contains #'s that were in #2 but didn't match #1.
make any sense?

couldn't get that to work. Column A has data from A2:A913, Column B is
B2:B1657. There's about 700r ecords in Column B that aren't in A. I'm
hoping to find those that don't match A and move to new column.

I did select C and D down to 1657, but when I went to hide , C and D were
hidden. Did i miss something?

"Max" wrote:

One play which drives it out ..
Assume source data in cols A and B, in row 2 down
In C2: =IF(B2="","",IF(ISNUMBER(MATCH(B2,A:A,0)),"",ROW() ))
Leave C1 blank
In D2:
=IF(ROWS($1:1)COUNT(C:C),"",INDEX(B:B,SMALL(C:C,R OWS($1:1))))
Select C2:D2, copy down to cover the max expected extent of data in col B,
say down to D5000? Minmize/hide col C. Col D will return what you seek.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DRKENNE" wrote:
I have two columns with 1000+ numbers in them. Column 1 is the correct
column. I want to compare columns 1 and 2 and move those in #2 that don't
match or appear in #1 to a third column. End result should be #' 1 and 2
match and a third column contains #'s that were in #2 but didn't match #1.
make any sense?

As mentioned in my response, only col C needs to be minimized/hidden.
Col D is the results col.

Anyway, here's a link to a working sample for your easy reference:
http://www.savefile.com/files/1571748
Extract numbers in List B not in List A.xls

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DRKENNE" wrote:
couldn't get that to work. Column A has data from A2:A913, Column B is
B2:B1657. There's about 700r ecords in Column B that aren't in A. I'm
hoping to find those that don't match A and move to new column.

I did select C and D down to 1657, but when I went to hide , C and D were
hidden. Did i miss something?

Thanks that worked


"GoBow777" wrote:


DRKENNE;670879 Wrote:
I have two columns with 1000+ numbers in them. Column 1 is the correct

column. I want to compare columns 1 and 2 and move those in #2 that
don't
match or appear in #1 to a third column. End result should be #' 1 and
2
match and a third column contains #'s that were in #2 but didn't match
#1.
make any sense?

This formula will show the unique numbers in column B that are not
found in column A; paste it in cell C2 and copy down.

=IF(COUNTIF($A:$A,B2)=0,B2,"")




--
GoBow777