Comparing tables in different files

Ric F

Board Regular
Joined
Apr 15, 2011
Messages
107
Office Version
  1. 365
Hello all!
I'm trying to compare two tables - one from last month (table A) and one from this month (table B). I want to see the differences in this months (table B) file from last. Both tables are identical in columns (A:T). What formula do I use to compare these and find the newly added records?

Ric
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Can you please post some sample data, along with expected outcome, using the XL2BB add-in?
 

Ric F

Board Regular
Joined
Apr 15, 2011
Messages
107
Office Version
  1. 365
Table B:
Item NumberExpiration DatePINUser 1User 2ID
55531​
1407140395​
55194​
5/31/2021 0:00​
1508151663​
MD-35055H86448
1548230832​
1386649341​
44626​
1205191210​
123456​
12/31/2020 0:00​
4567890123​
55841​
1154404549​
1205086998​
5101022815​
12/31/2020 0:00​
1427409887​
DO-02922F77928
1831127562​
R-10151
1417362666​
23626​
1174697502​
MD-44547
1972701050​
H01011
1760583066​
H36054
1932199395​
MD-40683
1154614832​
R-11569
6/30/2020 0:00​
1265099907​
Table A:
Item NumberExpiration DatePINUser 1User 2ID
55531​
1407140395​
55194​
5/31/2021 0:00​
1508151663​
MD-35055H86448
1548230832​
1386649341​
44626​
1205191210​
1265892046​
55841​
1154404549​
1205086998​
5101022815​
12/31/2020 0:00​
1427409887​
DO-02922F77928
1831127562​
R-10151
1417362666​
23626​
1174697502​
MD-44547
1972701050​
H01011
1760583066​
H36054
1932199395​
MD-40683
1154614832​
R-11569
6/30/2020 0:00​
1265099907​
so result should be:
Item NumberExpiration DatePINUser 1User 2ID
123456​
12/31/2020 0:00​
4567890123​
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Do you have the Filter & Unique functions?

Please update your account details to show which version of Excel you are using, as this affects which functions you can use. Thanks
 

Ric F

Board Regular
Joined
Apr 15, 2011
Messages
107
Office Version
  1. 365

ADVERTISEMENT

I am using Office 365
Not sure what you mean by Filter & Unique functions, so I'm guessing not.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Ok, thanks for that.
Two formulae, one if you have the dynamic array functions(in blue) & another if you haven't had the update yet (in green)

+Fluff New.xlsm
ABCDEFGHIJKLMNO
1Item NumberExpiration DatePINUser 1User 2IDItem NumberExpiration DatePINUser 1User 2ID
2555311407140395555311407140395
35519431/05/2021 00:0015081516635519431/05/2021 00:001508151663
4MD-35055H864481548230832MD-35055H864481548230832
513866493411386649341
6446261205191210446261205191210
7126589204612345631/12/2020 00:004567890123
8558411154404549558411154404549
912050869981205086998
10510102281531/12/2020 00:001427409887510102281531/12/2020 00:001427409887
11DO-02922F779281831127562DO-02922F779281831127562
12R-101511417362666R-101511417362666
13236261174697502236261174697502
14MD-445471972701050MD-445471972701050
15H010111760583066H010111760583066
16H360541932199395H360541932199395
17MD-406831154614832MD-406831154614832
18R-1156930/06/2020 00:001265099907R-1156930/06/2020 00:001265099907
19
20
21
22
23Item NumberExpiration DatePINUser 1User 2IDItem NumberExpiration DatePINUser 1User 2ID
24123456441960004567890123123456441960004567890123
25      
26      
27
28
Main
Cell Formulas
RangeFormula
A24:F24A24=FILTER(J2:O18,(ISNA(MATCH(J2:J18,A2:A18,0)))*(J2:J18<>""))
J24:O26J24=IFERROR(INDEX(J$2:J$18,AGGREGATE(15,6,(ROW($J$2:$J$18)-ROW($J$2)+1)/(ISNA(MATCH($J$2:$J$18,$A$2:$A$18,0)))/($J$2:$J$18<>""),ROWS(J$2:J2))),"")
Dynamic array formulas.
 

Ric F

Board Regular
Joined
Apr 15, 2011
Messages
107
Office Version
  1. 365

ADVERTISEMENT

OK - So I tried the formula in A24 and it seemed to recreate my table. I tried the formula in J24 changing the "15,6" to the number of rows and columns and nothing populated at all.
There also is a difference in this months data of 218 additional rows.
So I would have expected 218 records to populate the formula table
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
You need to leave the 15,6 as it is, it's nothing to do with rows/columns, however that will do the same as the filter formula.
If the filter formula is recreating the entire table, then the values in the first column of each table don't match.
 

Ric F

Board Regular
Joined
Apr 15, 2011
Messages
107
Office Version
  1. 365
Nothing populates with the 15,6 either
My current table is in A1:T49944 and I'm comparing it to last months table in AA1:AT49726 so the formula is reading as:
=IFERROR(INDEX(A$2:T$49944,AGGREGATE(15,6,(ROW($A$2:$T$49944)-ROW($A$2)+1)/(ISNA(MATCH($A$2:$T$49944,$AA$2:$AT$49944,0)))/($A$2:$T$49944<>""),ROWS(A$2:A2))),"")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
It should be
=IFERROR(INDEX(A$2:A$49944,AGGREGATE(15,6,(ROW($A$2:$A$49944)-ROW($A$2)+1)/(ISNA(MATCH($A$2:$A$49944,$AA$2:$AA$49944,0)))/($A$2:$A$49944<>""),ROWS(A$2:A2))),"")

Although if you have the filter function, I would urge you to use that as it will be a lot faster, especially over the best part of 50,000 rows.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,386
Messages
5,636,015
Members
416,892
Latest member
Bensch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top