Mac Excel users

Hello!
Are there any downloads or programs that can help me compare two different, yet similar workbooks? I need to check one against the other, and make sure all formulas are referencing the correct cells, etc.

I am looking for an automated solution, if possible. I have seen many PC downloads for such programs, but none for Macs...

Most PC code will run on a Mac. Download the existing and modify would be a decent route.

One problem you might have to deal with is

Please Login or Register  to view this content.

Where do I put the code? I don't do Visual Basic or VLookup, etc...not yet although I guess I need to learn!

Originally Posted by virtualadmin

...I don't do Visual Basic or VLookup, etc...not yet although I guess I need to learn!

Well in that case,...I'll write a routine that check two worksheets to see if their formulas match each other. I'm on lunch break at work right now, so it will be a few hours until I get a chance to do this.

(This sounds like its going to be a fun routine to write.)

I am in awe of how kind and generous that is. Thanks so very much

Is this a one-time situation or are you looking for something that can be used in various situations.

Ideally, the ability to use it in multiple situations would be very nice. However, in the immediate future, it's a one time situation.

Thanks

OK, This is my current plan.

We are creating a Comparing Workbook that will look at two other workbooks and verify that their formulas match. (Values can be different.)

The sheets need to be named the same in each book. It is not clever enough to notice that someone SavedAs and renamed the sheets, but left the formulas alone. (I think I could be made it that clever, but it would be deadly slow.)

If there are any mismatches, they will be noted on a sheet in Workbook("Compare").

If these restrictions need to be changed, now is the best time to do that.

Also, aprox. how many sheets per book? I think that I can deal with 500ish sheets per book but its still good to know.

Thanks!

There are only 3 sheets in each workbook and they are both identically named.

Thanks so much for doing this!

OK, its still got a few bumps, but this is what I have. I'm not sure how experienced you are so these are detailed instalation instructions:

Quit Excel, re-start and open a new workbook.
Through the Tools Menu, Macro open the Visual Basic Editor.
Through the Insert menu, insert a Module (NOT a "Class Module")
Remove any thing in the module, like "Option Explicit"
Paste this into that module. It holds the main comparison routines:

Please Login or Register  to view this content.

Continued...

Through the Insert menu, insert another Module. Paste this into that module. These are the routines that drive the user interface.

Please Login or Register  to view this content.

continued ...

Once again, insert a Module and paste these routines into that one. These are the start up routines that draw controls and write event coding.

Please Login or Register  to view this content.

Saving the workbook would be a good idea.
Use the VB editors File menu(Excel'98) or Excel menu (2004) to "Close and return to Excel"
Use the Tools - Macro - Macros... to run the macro "BrandNewBook". Save As when prompted. The routine is now ready to go.

To use the routine:
On the ControlPanel sheet, open workbooks, either with Excel or use the button provided.
Select one workbook in each of the two list boxes.
Click the Compare button (suprise!) and they will be compared.
The first three columns of the displayComparison sheet will show a summary by sheets. A sheet will either be unique (no sheet with the same name in the other book), match or MisMatch. The other columns of displayComparison show the contents of those cells that don't match the other sheet. If you double click the summary line of a sheet, you will be taken to the detailed listing for that sheet.

Also, any of the controls can be moved resized etc., button captions can be changed.

Things I need to fix. 1) There are cases where a mismatched cell might be listed twice. 2) The cells are sorted alphabeticaly (A1,A10,A2,B1) it could (should) be sorted bi-modaly A1,A2,A10,B1. 3)Closeing a file should automaticly update the list boxes. As it is, you may have to click the Cancel button to update the list boxes. 4)The Re-Check button on the displayComparison box does not verify that the workbooks are still open, before doing a re-check (and possibly crashing).

Oh, yes one final thing. This routine is not for the PC. If run on a PC, there might be an error in the sub checkArea.

I hope it does what you want it to.

That is awesome. I can't believe you did that for me. Thank you so much

My pleasure. Writing the instal routine taught me a lot. It was fun.