Compare list of number between two columns
 

I have two columns with a list of numbers in each. I need to compare the
list in column A to the list in column B and produce a list of numbers that
is in A but not in B. Can that be done by using a formula?

Compare list of number between two columns
 

Try this:

Assume the range is A1:B10 and there are no empty cells within the range.

Enter this formula** in C1. This will return the count of numbers that meet
the criteria.

=SUMPRODUCT(--(ISNA(MATCH(A1:A10,B1:B10,0))))

Enter this array formula** in D1 and copy down until you get blanks. This
will extract the numbers that meet the criteria.

=IF(ROWS(D$1:D1)<=C$1,INDEX(A$1:A$10,SMALL(IF(ISNA (MATCH(A$1:A$10,B$1:B$10,0)),ROW(A$1:A$10)-MIN(ROW(A$1:A$10))+1),ROWS(D$1:D1))),"")

Or, you could combine both formulas into a single array formula** :

=IF(ROWS(D$1:D1)<=SUM(--(ISNA(MATCH(A$1:A$10,B$1:B$10,0)))),INDEX(A$1:A$10 ,SMALL(IF(ISNA(MATCH(A$1:A$10,B$1:B$10,0)),ROW(A$1 :A$10)-MIN(ROW(A$1:A$10))+1),ROWS(D$1:D1))),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Theresa" wrote in message
...
I have two columns with a list of numbers in each. I need to compare the
list in column A to the list in column B and produce a list of numbers
that
is in A but not in B. Can that be done by using a formula?

Compare list of number between two columns
 

This will omit duplicate entries, however It does leave "blank" cells is the list.
Data in A1:B10...

=IF(AND(COUNTIF($B$1:$B$10,A1)=0,COUNTIF($A$1:A1,A 1)=1),A1,"")
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Theresa"
wrote in message
I have two columns with a list of numbers in each. I need to compare the
list in column A to the list in column B and produce a list of numbers that
is in A but not in B. Can that be done by using a formula?