Excel Two Columns With Duplicates

Question

So I have two columns in excel with column A containing nearly the exact same data as column B.

I need a way to Match column A with Column B and any values that are the same in Column A and Column B need to be removed from Column B.

So Column A has 11,592 product SKU numbers.

Column B has 12,555 product SKU numbers.

And I need a way to get the SKU product numbers from Column B that are not in Column A. Maybe put them into Column C?

Answer 1

In cell C1 use this formula:

=IF(VLOOKUP(B1,A:A,1)=B1,"",B1)

Copy and paste it to all rows that have a value in column B and it will show the unique values.

Then copy column C to column D by Paste Values so that you can sort it / filter out blanks.

Answer 2

I'm making a couple of assumptions here to allow me to answer the question. You may need to adjust the cells accordingly:

• Your column A data is in cells A1:A11592
• Your column B data is in cells B1:B12555

We need to setup column C to show the values in B which are not in A. We'll do this with a formula in each cell of C1:C12555 (one cell for each value in col. B we will test). As a second step we can sort column C to put the found values at the top of the list.

1. Create a formula in cell C1: =IF(ISNA(VLOOKUP(B1,$A1:$A11592,1,FALSE)),B1,"")
2. Copy C1 to all cells C1:C12555 (see tip at end)

Now each cell in column C contains the value next to it from Column B if that value doesn't occur in Column A, or a blank (an empty string) if it does occur. To get all of the values in col C together, you can select columns B and C together, and sort on column C.

TIP: to quickly copy C1 to over 12000 rows, try this:

1. select cell C1, press Ctrl-C (Command-C on Mac) to copy.
2. Using the arrow keys, move left to cell B1.
3. Press the "end" key, and the the "down arrow" key. This will jump you to the last non-blank value in column B (i.e., the "end" of the column). This assumes that you don't have any blank values in the middle of column B's data.
4. Using the arrow keys, move right to column C (should be cell C12555).
5. Holding the SHIFT key, press "end" and "up arrow" to jump back to the top of column C; since you held SHIFT the cells are all selected.
6. Press Ctrl-C to paste the copied function to all cells.