Geek Answers Handbook

Excel Two columns with duplicates

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

I need a way to map column A to column B, and any values ​​that are the same in column A and column B should be removed from column B.

So column A has 11,592 product SKUs.

Column B contains 12,555 product SKUs.

And I need a way to get SKU product numbers from column B that are not in column A. Perhaps put them in column C?

+8
excel
source share
2 answers

In cell C1, use this formula:

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

Copy and paste it into all the rows that have a value in column B and unique values ​​will be displayed.

Then copy column C to column D using paste values ​​so you can sort / filter spaces.

+9
source share

I make a few assumptions here to allow me to answer the question. You may need to configure the appropriate cells:

  • Your column Data is in cells A1: A11592
  • The data for your column B is in cells B1: B12555

We need to set column C to show values ​​in B that are not in A. We will do this using the formula in each cell C1: C12555 (we will test one cell for each value in column B), As a second step, we we can sort column C to put the values ​​found at the top of the list.

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

Now each cell in column C contains a value next to it from column B if that value does not appear in column A or empty (empty row) if it really occurs. To get all values ​​from col C together, you can select columns B and C together and sort by column C.

TIP: to quickly copy C1 to over 12,000 lines, try the following:

  • select cell C1, press Ctrl-C (Command-C on Mac) to copy.
  • Use the arrow keys to move left to cell B1.
  • Press the end key and the down arrow key. This will move you to the last non-empty value in column B (i.e., the "End" of the column). This assumes that you do not have null values ​​in the middle of the data in column B.
  • Using the arrow keys, go straight to column C (should be cell C12555).
  • While holding down the SHIFT key, press “end” and “up arrow” to return to the beginning of column C; since you held down SHIFT, all cells are selected.
  • Press Ctrl-C to paste the copied function into all cells.
+5
source share

More articles:


All Articles