Microsoft Excel – How to Copy Unique Values from Two Columns into a Third Column

microsoft excel

I have a master list of orders in a spreadsheet.

Column A lists all orders. Column B lists a subset of all orders. If both lists were small, I could manually do this but I have hundreds of records to look through.

What is the best way to find values from Column A that do not exist in Column B, and place them in Column C?

Visually, I am looking to create a Column C like this:

Column A       Column B       Column C
A1             A3             A1
A2             A5             A2
A3                            A4
A4
A5

Note that I am not seeking to merely highlight unique values in Column A, but I am looking to copy those unique values into a new column (C).

Best Answer

Try:

=IFERROR(INDEX($A$1:$A$100,AGGREGATE(15,6,1/(1/(ISNA(MATCH($A$1:$A$100,$B$1:$B$100,0))*ROW($A$1:$A$100)*($A$1:$A$100<>""))),ROWS($1:1))),"")

enter image description here

Related Question