0

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
2

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

  • 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
0

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:

enter image description here

0

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

enter image description here

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

Hope this help you.

  • Do you know why it's adding an empty cell in Col C? – a coder Feb 1 '18 at 13:41
  • Yeah,, I do,, since the Formula is matching like A2 with B2 , A3 with B3 and so down wards. Therefore found A1, A2 & A4 not available in Column B are Unique. – Rajesh S Feb 2 '18 at 6:05
  • Ah, got it. I wish there was a way to remove the "blank" values in Col C. Filtering isn't working for me since there is a formula in the cell and not exactly empty, but also not showing any data. – a coder Feb 9 '18 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.