Consolidate unique rows across multiple tables AND detect when unique row not repeated on next table

[justtryingtolearn]

Every week, my client publishes an updated version of the same workbook. Every new version contains additions, removals, and changes to the data inherited from the previous version. What I need to do quarterly is combine all these workbooks from the given quarter into one, but I cannot simply append every record as this blows past Excel's 1,048,576 row limit (each version contains about 100,000 rows). What I need instead is to grab only the unique rows across every version. One column on each workbook is dedicated to serving as a redundant workbook version date. If a record stayed the same from versions 1/1/2022 to 1/8/2022, I would need a way of ignoring this column or else it would be detected as a change and get appended despite no real change occurring. In addition to grabbing unique rows, I also need to see when a record dropped out from one version to another. In summary, I need to know what was added, removed, or changed between versions and when.

Version 1

Book2.xlsx
	A	B	C	D
1	Version Date	Product ID	Availability	QC
2	1/1/2022	1001	In Stock	Y
3	1/1/2022	1002	Not Available
4	1/1/2022	1003	In Stock	Y
5	1/1/2022	1004	Not Available
1.1.2022

Version 2

Book2.xlsx
	A	B	C	D
1	Version Date	Product ID	Availability	QC
2	1/8/2022	1001	Not Available	Y
3	1/8/2022	1002	Not Available
4	1/8/2022	1004	Not Available
1.8.2022

Desired Consolidated View

Book2.xlsx
	A	B	C	D	E
1	Version Date	Product ID	Availability	QC	Date Removed
2	1/1/2022	1001	In Stock	Y
3	1/8/2022	1001	Not Available	Y
4	1/1/2022	1002	Not Available
5	1/1/2022	1003	In Stock	Y	1/8/2022
6	1/1/2022	1004	Not Available
Consolidated

 

[severynm]

Hi! Have you thought about loading the data in through Power Query? Data in the PQ data engine is not subject to excel's row limits. You can combine all of your workbooks into one query and then pretty easily filter out all the duplicates and output the remainder to the worksheet (assuming the number of uniques is also <1M).

 

[justtryingtolearn]

Hi! Have you thought about loading the data in through Power Query? Data in the PQ data engine is not subject to excel's row limits. You can combine all of your workbooks into one query and then pretty easily filter out all the duplicates and output the remainder to the worksheet (assuming the number of uniques is also <1M).

I am a Power Query newb, but I have successfully consolidated unique rows and removed duplicates (while ignoring the date column), but I haven't yet been able to account for removals. There isn't a
Date Removed column present in the workbooks, I just added that in on the Desired Consolidated View. Any ideas how to get a Date Removed column up and running?

 

[severynm]

I am a Power Query newb, but I have successfully consolidated unique rows and removed duplicates (while ignoring the date column), but I haven't yet been able to account for removals. There isn't a
Date Removed column present in the workbooks, I just added that in on the Desired Consolidated View. Any ideas how to get a Date Removed column up and running?

After taking a closer look, in your consolidated table from the first post, should the date removed 1/8/22 belong in the row for 1002 instead of 1003? Item 1002 was unavailable on 1/1 and also on 1/8 (no change), so the 1/8 date should be ignored, correct? And shouldn't there also be a 1/8 for item 1004 in the consolidated table?