-1

In Excel, how can I get the difference between 2 tables?

I have 2 tables: A and B.
B is a subset of A. In other words, all rows/records of B are included in A, but not vice versa.

I would like to get
A - B
i.e. I want an output which gives me only the records which are in A but not in B.

Also, more generally, if B were not a subset, how would I get
A∪B - A∩B?

Community
  • 1
  • 1
OC2PS
  • 1,037
  • 3
  • 19
  • 34
  • You could compare them with `IF` statements. That's all I can give you with how much information you've provided. – ashleedawg Jun 07 '18 at 06:13
  • @ashleedawg Thanks! But I am not sure I understand, how exactly an IF statement would be useful here. At the moment, I have the 2 "tables" in different files/workbooks. But I could easily get them into the same workbook as different worksheets, or even in the same sheet as different ranges... – OC2PS Jun 07 '18 at 06:17
  • 2
    @ashleedawg I don't see how this is a duplicate of https://stackoverflow.com/questions/7074/what-is-the-difference-between-string-and-string-in-c?rq=1 – OC2PS Jun 07 '18 at 06:30
  • You could add a helper column in Table A using `=MATCH` to check if it is also in Table B and then filter on that helper column. – Pᴇʜ Jun 07 '18 at 06:40
  • 1
    Thanks @Pᴇʜ I did end up using this rather mechanical way...`=IF(ISERROR(MATCH(A1,$B$1:$B$19902,0)),"Unique","Duplicate")`, then filtering and copy pasting....the tables weren't big....about 20k rows, so it worked. – OC2PS Jun 07 '18 at 19:31

1 Answers1

2

I usually use VLOOKUP to achieve this if this is going to be made only for once for a dataset. Just write a VLOOKUP fomula on the big table, the rows that are not in the small table will return #N/A error. When we filter out that error rows, we will have the minus'd rows left behind.

On the other hand this is also achievable using Power Query which is a cleaner way IMHO. For Excel 2010 you should download and install it. For the newer versions Power Query is included in Excel natively.

I am able to explain the process for Office 365 since I have that version; for previous versions slight changes may apply.

  • First get your tables into Power Query using Data / From Table/Range menu.
  • When you have your both tables to Power Query, right click on a blank space at the Queries pane at the left and go to New Query / Combine / Merge Queries as New menu:

enter image description here

  • In this screen, select your tables (select the larger table in the first place), CTRL select the table fields to be minus'd and select Left Anti in the bottom combo. When you OK this you will have a minus'd new table.

enter image description here * Select Close & Load in the Home menu and your new table will be available in a new sheet in Excel. * When there is a change made in the original sheets, just press Data / Refresh in Excel and your generated table will be refreshed accordingly.

Hakan ERDOGAN
  • 1,150
  • 8
  • 19