Forum Discussion
Index / Match Fucntion Failure
I am creating a tool to compare data tables. An original table resides on one tab, while an updated version of the table is copied onto another tab and compared with the original.
Each table (the updated and the master) have about 30 columns. To compare the entries, I am concatenating all columns (i.e., A1-B1-C1-D1...) and comparing them via index/match to the concatenated columns of the master list (i.e. =IF(concatenation_1=concatenation_2,"same","different").
For some reason, however it doesn't work consistently. It works for most entries, but for some it doesn't. I've tried:
- Copy/pasting the exact same data into both tables to validate the formulas, and there are still discrepancies. I swear the concatenation formulas AND the data are identical - I've checked MULTIPLE times.
- Format Painting entries in both tables so their formatting matches. Doesn't seem to matter.
Any ideas? Could it be a problem with the base file from which I'm copying?
Thanks,
Well, in general I'd trust Excel to be doing the comparisons correctly.
I'd look for discrepancies in the two sets of data. Have you tried doing the comparisons with the data in each of the 30 columns without doing that concatenation? If there are discrepancies, they're going to originate in a column, so they'd be more readily tracked down by comparing at that level.
And time and again the discrepancies in a case like this can be trailing spaces--invisible to the human eye but glaringly obvious to the computer. Or special characters, or numbers that are text in one case but numbers in another.
Anyway, my main point is to assume that there is a discrepancy, not to assume that Excel is failing.
Is it possible for you to attach a copy of the spreadsheet, or a representative portion of it? Without that, all we can do is offer generalizations, such as I've done above
