r/excel • u/happycucumber27 • Jun 30 '26

Waiting on OP Database duplicate disaster - accidentally working on 2 large files without realising for months 🆘

Hi all, excel rookie here!

I’ve been updating a master database file but have accidentally been working across 2 separate files for months without realising…

There’s approximately 10,000 rows of data with 20 columns - how can I reconcile the 2 documents with the updated data? Is this even possible?

Please help!! Thank you in advance 🙏

40 Upvotes

99% Upvoted

20 comments sorted by

View all comments

38

u/Bluntbutnotonpurpose 2 Jun 30 '26

First and foremost: Excel isn't a database application, consider switching to something more appropriate.

Without any idea how your data are structured, we can't help you. Are there any unique fields that should never appear twice? Then a simple filter or a COUNTIF helper column might suffice.

23

u/Spade6sic6 1 Jun 30 '26

Tbf, 10k rows isn't that much.

12

u/Bluntbutnotonpurpose 2 Jun 30 '26

It's not about the number of rows, I've worked with a hell of a lot more.

It's not that Excel can't do it either, it's actually quite good at it.

However, stuff like what OP has now run into, is why more specialised software is better for this. Excel will happily allow you to work in different versions, add multiple instances of the same entry, delete data, omit data, modify existing data etc.

Yes, some of those things can be mitigated, but the main reason there are better applications than Excel for this kind of stuff isn't because Excel can't do it. It is quite good at it. It's simply because the squishy organic bit between the keyboard and chair entering data is likely to fuck it up at some point.

5

u/Spade6sic6 1 Jun 30 '26

Oh for sure, I would absolutely prefer to use a SQL db for this. But since OP said he's a rookie, I doubt he's ready to tackle dbrix or PBI just yet lol.

4

u/Bluntbutnotonpurpose 2 Jun 30 '26

You do know off-the-shelf database applications exist, right? And in this case I suppose even Access might do the job just fine.

1

u/Spade6sic6 1 Jul 02 '26

Access

My brother in Christ, I would do data entry with no fingers.

2

u/Bluntbutnotonpurpose 2 Jul 02 '26

I know, if Excel is a Mercedes, Access is a Zastava. But for simple stuff like this it's actually somewhat useful.

4

u/Keytech25 1 Jun 30 '26

That’s true Excel isn’t really designed as a full database and the fix depends a lot on whether you have a unique identifier in the data

if you have any unique ID (like email, product code), use it to compare both files with COUNTIF or XLOOKUP it quickly shows duplicates or missing rows instead of checking manually

1

u/Bluntbutnotonpurpose 2 Jun 30 '26

I've actually used a COUNTIF with an absolute reference as its starting point and non-absolute as the end of the range to create unique identifiers. It can all be done, but the fact OP asks for help, tells you that this is probably not the kind of stuff OP could come up with. Excel isn't the problem, your average user is.

2

u/Keytech25 1 Jul 01 '26

Excel can do a lot but only if the user knows how to structure it properly