# Copy unique values from two columns into a third column

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 way is to use `VBA` rather than regular formulas.. – Drakonoved Jan 31 '18 at 21:11
• Your requirement is simple COMPARE column A with B and extract unique values in Column C. So better edit the TITLE. – Rajesh S Feb 1 '18 at 5:59
• And check the solution I've posted. – Rajesh S Feb 1 '18 at 6:20

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))),"")
``````

• Wish this formula could copy to column C without the empty cells. – a coder Feb 9 '18 at 16:25
• @acoder I don't understand what you mean – Ron Rosenfeld Feb 9 '18 at 16:28
• Here's my example sheet. This may be an OpenOffice thing, but I'm left with blank values between cells in column C. – a coder Feb 9 '18 at 19:18
• @acoder Perhaps you mean `Libre Office` as I do not believe `Open Office` has the `IFERROR` function. In any event, if I open the file you uploaded in Excel, there are no blanks in column C -- it displays as shown in my screenshot. So, clearly, it is a Libre Office thing. Unfortunately, the two are not identical. – Ron Rosenfeld Feb 9 '18 at 20:11
• My mistake, it is Libre Office. Ended up just find/replacing the blank rows in gedit, then pasting back into the sheet in a different column. – a coder Feb 9 '18 at 21:11

This is very easy with a "helper" column. In D1 enter:

``````=IF(ISERROR(MATCH(A1,\$B\$1:\$B\$5,0)),1,"")
``````

and in D2 enter:

``````=IF(ISERROR(MATCH(A2,\$B\$1:\$B\$5,0)),1+MAX(\$D\$1:D1),"")
``````

and copy down. Column D marks each row that contains useful data with a unique ID. Column D tells use first get data from row#1, then get data from row#2, then get data from row#4

Then in C1 enter:

``````=IFERROR(INDEX(\$A\$1:\$A\$5,MATCH(ROWS(\$1:1),D:D,0)),"")
``````

and copy down:

I would like to suggest you a simple VLOOKUP Formula, will compare both Columns A & B and extract unique vales in Column C.

``````=IF(ISNA(VLOOKUP(A2,\$B\$2:\$B\$6,1,FALSE)),A2,"")
``````