I'm trying to compare two Excel files and store what's only there in the new file in one sheet and store what is only there in the old one in another sheet. (Basically new - old = sheet1
and old - new = sheet2
.) One SO answers suggest to loop through all cells and do a simple comparison. I'm (very) new to VBA and Macros so I don't know how to do this. How is it done (or where can I learn this)?
A very simple check you can do with Cell formulas:
Sheet 1 (new - old)
=(if(AND(Ref_New<>"";Ref_Old="");Ref_New;"")
Sheet 2 (old - new)
=(if(AND(Ref_Old<>"";Ref_New="");Ref_Old;"")
This formulas should work for an ENGLISH Excel. For other languages they need to be translated. (For German i can assist)
You need to open all three Excel Documents, then copy the first formula into A1 of your sheet 1 and the second into A1 of sheet 2. Now click in A1 of the first cell and mark "Ref_New", now you can select your reference, go to the new file and click in the A1, go back to sheet1 and do the same for "Ref_Old" with the old file. Replace also the other "Ref_New".
Doe the same for Sheet two.
Now copy the formaula form A1 over the complete range where zour data is in the old and the new file.
But two cases are not covered here:
To cover this two cases also, you should create your own function, means learn VBA. A very useful Excel page is cpearson.com
Source: Stackoverflow.com