Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare and move numbers
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? |
#2
|
|||
|
|||
Quote:
=IF(COUNTIF($A:$A,B2)=0,B2,"") |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare and move numbers
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare and move numbers
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare and move numbers
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
compare and move numbers
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare a list of numbers | Excel Worksheet Functions | |||
compare a list of numbers | Excel Worksheet Functions | |||
VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
how can i move the starting point for the numbers numbers down. | Setting up and Configuration of Excel | |||
How to compare two cells for the same numbers | Excel Worksheet Functions |