Corrupted Column between archived spreadsheet and present one

[Zarathrustra]

Having just become aware that at least one of my 450 column variables (with 3,000 entries) has corrupted data in it, I wish to check each variable against previous archived versions to try to correct this.

Is there an easy way to do this - to check for consistency between each column in one spreadsheet and another (archive) file?

I'm running Excel 2103, now in W10.

p.s. I thought Inquire might do this, but if so, I couldn't figure out how. But anyway, Inquire said it couldn't open the spreadsheet (even though it was already open and saved).

[NickyC]

Hi

why not just open both files and add a formula that checks if cell contents are identical, eg

=A1=[otherfile.xlsx]Sheet1!A1

[Zarathrustra]

Thanks for the idea Nicky, but unless I'm missing something in your idea, that would require me doing that for every one of the 3,000 entries - in each column. Which is just what I'm hoping to avoid, as I don't think I could face doing that.

I realise I could do a kludge wherein I inserted a column adjacent to the suspect column and do, as you suggest, a quick check down alongside for each cell. But to then have to repeat that for all 450+ columns does get very time consuming. In the end, I may have to do that, as the integrity of my data is critical to all of my research. But I'm hoping for a quicker and more elegant solution.

[NickyC]

I may be misunderstanding your issue - wouldn't you just copy the formula down into all the rows in the column? It would take very little time.

even with lots of columns , if the layouts of the two sheets are identifcal (same variables in same rows/columns) then just copying the formula would perform your check in a few seconds in a new sheet.

If the layouts aren't identifcal it will be a bit trickier, though.

[Zarathrustra]

Thanks again Nicky.

Yes, I could do that. But then I'd still have to do it column by column (all 450+); and alas, yes, the order of the columns have changed (some added, some removed, some different order). Plus I'd still then have to eyeball each and every one of the 3,000 x 450+ cells. Very time consuming.

[NickyC]

ok - is there a systematic way to tell which comparable variables are in which columns? For example, do the columns have the same title in each workbook, even if they are in different sheets or column orders? if so, it may be possible to write a macro to compare them. If not, I think you're stuck at doing it one column at a time.

[newdoverman]

NickyC's idea will work easily. Create an empty worksheet and in A1 of the new worksheet enter NickyC's formula =A1=[otherfile.xlsx]Sheet1!A1
Fill the formula across 450 columns and down as many rows as required for your data.

All the FALSE entries are the mismatched data. You can highlight all the FALSE entries by selecting the data on the new worksheet with the TRUE AND FALSE using Conditional Formatting, New Rule, Use Formula and enter =A1=FALSE in the formula field. Select a format for the cells and click OK, OK, APPLY, OK. All FALSE entries will be highlighted. The same cells in the other worksheets are where the problems are. To make the problem cells stand out on the other worksheets. With the new worksheet (with TRUE AND FALSE) the active worksheet, hold down the Ctrl key and click the worksheet tabs of the other worksheets. Then, apply a border to the false cells and that border will be applied to the same cells on the other worksheets.

[Zarathrustra]

Thanks both Nicky & newdoverman for the nudges. Whilst I was hoping for a simpler function such as would, say, compare two spreadsheet files and after noting where there was a difference in a particular cell(s), allow a quick choice as to which to accept.

My need to avoid a somewhat simple (tho' perhaps the best) check on each and every cell and just noting what the difference is in the archive is a not unreasonable half-way step (I would wish that value rather than a note that there is a difference) is relatively straight forward (as you both say). And so, not finding any other way, I have now done by copying both the present spreadsheet and the archive equivalent into a new spreadsheet entirely (the originals are multi-sheets with copious cross-links) and then a sheet of the differences (if any) of every cell.

The reason I was hoping for a solution akin to that I mentioned earlier is that if, as I suspect, I find I have to compare the present spreadsheet with many earlier archived ones, the process will get quite time-consuming. But it would appear there is no other than relatively simple way I've just described and so I will do it. The integrity of my data is paramount - subsequent analysis is meaningless without that basis.

Thanks again for helping.

[newdoverman]

If you want to use Window's File Compare utility, this site tells you how. I think that you would find this a real pain.
http://www.howtogeek.com/206123/how-...ommand-prompt/

[Zarathrustra]

Thanks. Now you mention it, I do recall FC from the early days - using MSDOS just to check overall file integrity. But, as you say, for this purpose it is virtually useless. Anyway, I do know there's many differences between my two files, not least that my present file has quite a few extra records/rows (which are probably OK).

The method I have now evolved is OK, just tedious (but better than trying to swivel eyeballs between the two sheets).

Thanks again.