Compare list of number between two columns

  • Thread starter Thread starter Theresa
  • Start date Start date
T

Theresa

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?
 
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)
 
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,A1)=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?
 
Back
Top