This should be simple in Excel right?

April 8, 2010 3:33 PM

Excel help. In column A I have several thousand numbers. In column B I have several thousand more numbers, some of which are in column A. I would like column C to contain the numbers from column A that are NOT in column B. I would also like column D to contain the numbers from column B that are NOT in column A.
posted by BigVACub to Computers & Internet (12 answers total)

In column C: =IF(A1=B1, "", A1)

In column D: =IF(A1=B1, "", B1)

Drag down to fill all the rows of columns C and D.

Let me know if that does what you want.
posted by Jacqueline at 3:43 PM on April 8, 2010

(oh, that example assumes you're starting on row 1 with no header row, if you're starting on a different row change the 1s to the row number)
posted by Jacqueline at 3:44 PM on April 8, 2010

Response by poster: I should have specified, the numbers are in no particular order (and even if I sort both columns, they still won't line up on the same row). So number 10 might be on row 5 of Column A, and row 6 of Column B. I don't want the number 10 to show up in either C or D since its in both A and B.
posted by BigVACub at 3:49 PM on April 8, 2010

Column C:
=IF(ISNA(MATCH(A1,$B$1:$B$end,0)),A1,"")

If you copy this down, will take the first value in A, look for it in B. If it's a match, a blank will appear in C, otherwise A will appear. Drag this down the entire length of A.

This will not sort out duplicates in A.

Column D:
=IF(ISNA(MATCH(B1,$A$1:$A$end,0)),B1,"")

Same idea for D.

Oh, Aend and Bend
is the bottom of the respective column. You can also just use A:A and B:B, sometimes that makes things slower though.
posted by defcom1 at 3:49 PM on April 8, 2010 [1 favorite]

Oops, forgot to close italics bracket.
=IF(ISNA(MATCH(B1,$A$1:$A$end,0)),B1,"")

Same idea for D.

Oh, Aend and Bend
is the bottom of the respective column. You can also just use A:A and B:B, sometimes that makes things slower though.

Don't forget the dollar signs. That locks column values, so when you drag the formula down, the range doesn't change.
posted by defcom1 at 3:52 PM on April 8, 2010

I think Jacqueline's answer assumes that the numbers that match are in the same row in A and B... If they're not, you can try something like this: (there's probably an easier way of doing it, but I haven't found it yet)

In C: =isna(match(a1,$b1:$b$X,0)
In D: =isna(match(b1,$a1:$a$X,0)
(Replace X with the last row of numbers in B and A, respectively...
Paste into all rows in C and D.

Then, use AutoFilter. In column C, select only the TRUE values, then copy the filtered list from column A to another sheet. Reset the filter for column C, and in column D, select only the TRUE values. Do the same thing with column B (copy to a new sheet). Turn off AutoFilter, then copy the columns from the new sheet back into the original as C and D.

Like I said, there's probably a better way...
posted by greatgefilte at 3:53 PM on April 8, 2010

And....defcom posted the better way... :)
posted by greatgefilte at 3:55 PM on April 8, 2010

Mines still got blanks in it, just realized. Easiest way off the top of my head is to copy the list to a new sheet, paste special 'values', use autofilter to show blanks, then delete those rows. There are more sophisticated ways, google 'delete blanks' in excel, but don't forget that the original list has formulas in it, it's not officially blank until you paste special values.
posted by defcom1 at 4:03 PM on April 8, 2010

As an alternative to the method above, you can use the VLOOKUP function.

But perhaps the most effective solution is to write a VBA macro.
posted by Simon Barclay at 5:10 PM on April 8, 2010

This assumes you're using Excel 2007.

Change the color of Column B numbers to something other than black (automatic) - say, red. On a new sheet, copy Column A and Column B into one column. Put a title in the first row and then highlight the range of values.

On the ribbon in the Home tab, click on the Conditional Formatting down arrow. Choose Highlight Cell Values .../Duplicate Values... and then choose the formatting (ie. Light Red Fill with Dark Red Text).

Now click on your title in cell and then choose the Data tab, then Filter in the ribbon.

Click the filter down arrow in cell A1 and choose Filter by Color/Filter by Font Color. If you choose Red, you're looking at unique values that were in Column B originally. If you choose Automatic, they would be unique values that were in Column A.

The ability to filter easily by color is great in Excel 2007! If you're using something older, you could probably accomplish this but not with built-in menus as far as I know.
posted by nelvana at 6:34 PM on April 8, 2010

VLOOKUP is dangerous. your numbers must be in ascending order. match(,,0) is much better. You can pair it with index() to do a vlookup in a list you can't order, it's more robust.

I wish we could upgrade to 2007 at work...
posted by defcom1 at 9:07 PM on April 8, 2010

defcom1, I don't believe you need to have the numbers in ascending order unless you want Excel to find the closest match. If you want to find an exact match, you can have any order you want - you just need to specify the range lookup as FALSE. I use VLOOKUP all the time on large spreadsheets and it works great but I am always looking for an exact match.
posted by nelvana at 9:47 PM on April 8, 2010 [1 favorite]