Matching excel files with differences

[aryadee]

Hi Gurus,

I have 2 formatted excel sheets with multiple equal tabs. I need to match them and highlight the differences.

I tried many macros which can match sheets just on plane data with no formatting at all column to column but formatted excel sheet matching is something different.

But I have below issue here with situations.

Impediments:
1- Excel file is GUI based formatted means multiple columns could be merged as single cell
2- graphical tables are present in excel sheet.
3-Difference highlighting can be in any way either coloring that cell which has difference or differences can be logged in other file whichever is easy part.



Please help....

[thelongpants]

Hi Aryadee,

Thanks for providing a sheet. It helps.
Questiomn: the activities in the column B are always the same for the sheets?

And can you provide an example of how you'd like to have the differences highlighted?

[aryadee]

I tell you the exact scenario :
1- we have some excel reports before and after db changes. So you can create before and after report from the above attached document as before.xls and after.xls after changing some data values inside them. That will simulates the scenario.

2-Difference highlighting can be in any way either coloring that cell which has difference or differences can be logged in other file whichever is easy part.

3- Biggest challenge is the formatting due to which multiple cells are clubbed to show some indentations or create only one cell etc.
Hence we have multiple report with multiple type of formatting.

4-One thing is sure before and after report are exact same formatting wise, just data value may be different..

[aryadee]

Gurus Any Help!!

@thelongpants

[thelongpants]

yes, working on it

[thelongpants]

Hi aryadee,

Find attached an updated workbook with a macro added.
This macro opens a userform to select the before and after sheet.
After selecting, press start.
In case there are not matching columns on after sheet, compared to the columns on before sheet, a report is shown.
In case columns can be found, the differences marked yellow.

Please let me know your thoughts.


Compare sheets v1.xlsm

[aryadee]

Hi Thelongpants,

Many thanks for the help. Please let me know if I am using it incorrectly.
Step-1 open compare sheets v1.xlsm
Step-2 open before and after sheet in background
Step-3 open run macro from from compare sheets v1.xlsm
Step-4 select before and after sheet tab to compare and click start
Step-5 Report should be generated.

But currently I am following all steps 1-4 but I am not getting any output. Please tell me I am doing it in wrong way...

[thelongpants]

Hi araydee,

I thinks steps executed are alright. Don't you see any marked cells in before and after sheets?

[aryadee]

Yeah I can see marked different cell thanks its a great help...Man!!

Before I start my work with it please let me know will it work for my all different formatted other reports as well or it is specific to this format on which it was created (i:e Pre_2.xlsm)?
Also does tab name "Blad1" has to do something with this macro in which report columns are mentioned in Column A and B. or I can delete it.

[thelongpants]

Hi,

It should work for different formatted sheets, although I've not tested it. So please double check results.

Details on the macro:

1. Find 'Gross Sales' on source sheet, determine range to check
2. Find 'Gross Sales' on target sheet (to make it work that this item might be on different row than on source sheet)
3. Combine the 2 header rows to solve the issue with merged cells into an array, to store the column numbers
4. For each cell in source range, check whether the column is found on target sheet
5. If found: validate it equals or mark it.
6. If not found: create an item for logging
7. After al cells validated, check whether error log has items
8. If error log has items: show screen, exit after button click
9. If error log has no items: exit

The sheet 'Blad1' can be removed, the macro's are stored in a Module, nog on a sheet.

[aryadee]

I tried on different format by changing 'Gross Sales' to header's first word of other report. So that report start range could be defined,
but it is doesn't seem working.

if we could set the range by default A1 to last cell where data is present then I guess it would be generic solution (required) which I am looking for.

Also other report may have columns, which are achieved by clubbing multiple columns.

[thelongpants]

Hi,

I updated the logic, because I noticed some differences in your new example sheet.
You can set the starting point in the userform now (so don't need to change code). For your example this should be "Prod1"

Let me know whenever new questions arises. :-)

Compare sheets v2.xlsm

[aryadee]

Hi TheLongPants

Thanks for the help sir!!

It is giving below error when I use "Prod1" (without double quotes) as Start Text-

Please Login or Register  to view this content.

Also when I tried giving "Products" as Start Text the also error is same.

Please let me know if am doing some wrong here.

[thelongpants]

Hi Aryadee,

I don't have any clue here. Seems the macro didnt start. You used my 'compare sheets v2' version?
This one worked with your last provided example where I found the 'Prod1'

I updated error handling to make sure the start text is found, or message else.

see attached:
Compare sheets v3.xlsm

[aryadee]

Many Thanks thelongpants, Now v3 file is working properly.

Also I tested it on some other formats, there it worked fine( highlighted the correct differences) also given me the error below

Please Login or Register  to view this content.

But, I highly appreciate your extended help, From here I can work on it to add other functionalities.

Good Luck!!