compare two column data and display which is missing

[9912271272]

Hi


I would like to compare data in two columns and display the data which is missing.

For EX:

A B

1 1

2 3

3 4


It should compare the column B values with column A values and the ouput should be 2.


Thanks in Advance

 

[vinodhraj]

Use vlookup. Syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])


For your problem, the formula is =VLOOKUP(A2,$B$2:$B$4,1,0).

To make it much better use =IF(ISNA(VLOOKUP(A2,$B$2:$B$4,1,0)),"match not found","match found")

Then filter column B with "match not found". The values in Column A in the filtered result is the missing ones... Hope this helps

 

[xld]

Try


=SUMPRODUCT(--(ISNUMBER(MATCH(B1:B3,A1:A3,0))))

 

[kensbro]

You can also use "conditional formatting" as follows:

Highlight the cells in column A... Go to Conditional Formatting>>New Rule>>Use Formula to determine which cells to format...In formula box type the following...=countif($B$2:$B$4,$A2)<1...Choose a color for your formatting, hit "Apply", then "OK".


Hope this helps,