There's a quiet frustration baked into the request from Legal-Scalion9502, and it's one we recognize all too well. They've got a solid handle on their data, 7,500 records, 100 columns, regular snapshots, but the tools they're using are fighting them at every turn. The formula they've landed on works only when the dataset is static, which is to say, almost never. As soon as records enter or leave the database, the whole comparison falls apart. That's not a user error. That's a fundamental limitation of the spreadsheet model when it's asked to do something it wasn't designed for.
What they're really describing is a need for identity-based comparison, not row-by-row matching. They need to track each record by its unique ID, then ask three simple questions: What changed? What's new? What's gone? The fact that they've had to jury-rig a formula that only works when nothing changes is a sign that the underlying approach is due for an upgrade. And they know it. They're not asking for a band-aid; they're asking for a better way to think about the problem. That's the kind of mindset we want to encourage, curiosity paired with a willingness to question the default.
The good news is that this isn't an unsolvable puzzle, even without VBA. The key is to stop comparing entire rows and start comparing values *within* a row, keyed to a stable identifier. With a lookup function, something like INDEX/MATCH or a dynamic array formula, you can pull the corresponding record from the older snapshot, then compare field by field. New records will simply not appear in the lookup, and missing records will return an error or a blank, which you can then flag. That gives you the three lists they're after, and it scales across any interval, whether it's daily, weekly, or monthly. The formula they've already written is a good start; it just needs to be pointed at the right reference point.
What stands out most here is their instinct to ask for the changed values to be highlighted, not just the record. That's the difference between a report and a tool. Anyone can see that a row is different; a thoughtful user wants to know *where* the difference lives. That's the kind of detail that turns a spreadsheet from a static archive into a living dashboard. And while conditional formatting can get you partway there, the real win is in structuring the comparison so the output itself tells the story. If you can see at a glance which columns shifted, you've saved yourself the tedious work of scanning 100 columns by eye.
Stop forcing the snapshot comparison into a same-day-only formula. Build your comparison around the unique ID, use lookups to pull in the prior state, and let the blanks and errors do the heavy lifting. You'll get your weekly and monthly snapshots without the headache, and you'll have a foundation that actually grows with your data. The solution isn't a single clever formula, it's a shift in how you approach the comparison itself. And that shift is well within reach.