Comparing tables in different files

[Ric F]

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

 

[Fluff]

Can you please post some sample data, along with expected outcome, using the XL2BB add-in?

 

[Ric F]

Table B:

Item Number	Expiration Date	PIN	User 1	User 2	ID
55531​					1407140395​
55194​	5/31/2021 0:00​				1508151663​
MD-35055		H86448			1548230832​
					1386649341​
44626​					1205191210​
123456​	12/31/2020 0:00​				4567890123​
55841​					1154404549​
					1205086998​
5101022815​	12/31/2020 0:00​				1427409887​
DO-02922		F77928			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 Number	Expiration Date	PIN	User 1	User 2	ID
55531​					1407140395​
55194​	5/31/2021 0:00​				1508151663​
MD-35055		H86448			1548230832​
					1386649341​
44626​					1205191210​
					1265892046​
55841​					1154404549​
					1205086998​
5101022815​	12/31/2020 0:00​				1427409887​
DO-02922		F77928			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 Number	Expiration Date	PIN	User 1	User 2	ID
123456​	12/31/2020 0:00​				4567890123​

 

[Fluff]

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]

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

 

[Fluff]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Item Number	Expiration Date	PIN	User 1	User 2	ID				Item Number	Expiration Date	PIN	User 1	User 2	ID
2	55531					1407140395				55531					1407140395
3	55194	31/05/2021 00:00				1508151663				55194	31/05/2021 00:00				1508151663
4	MD-35055		H86448			1548230832				MD-35055		H86448			1548230832
5						1386649341									1386649341
6	44626					1205191210				44626					1205191210
7						1265892046				123456	31/12/2020 00:00				4567890123
8	55841					1154404549				55841					1154404549
9						1205086998									1205086998
10	5101022815	31/12/2020 00:00				1427409887				5101022815	31/12/2020 00:00				1427409887
11	DO-02922		F77928			1831127562				DO-02922		F77928			1831127562
12	R-10151					1417362666				R-10151					1417362666
13	23626					1174697502				23626					1174697502
14	MD-44547					1972701050				MD-44547					1972701050
15			H01011			1760583066						H01011			1760583066
16			H36054			1932199395						H36054			1932199395
17	MD-40683					1154614832				MD-40683					1154614832
18	R-11569	30/06/2020 00:00				1265099907				R-11569	30/06/2020 00:00				1265099907
19
20
21
22
23	Item Number	Expiration Date	PIN	User 1	User 2	ID				Item Number	Expiration Date	PIN	User 1	User 2	ID
24	123456	44196	0	0	0	4567890123				123456	44196	0	0	0	4567890123
25
26
27
28
Main

Cell Formulas
Range	Formula
A24:F24	A24	=FILTER(J2:O18,(ISNA(MATCH(J2:J18,A2:A18,0)))*(J2:J18<>""))
J24:O26	J24	=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]

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]

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]

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]

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.