Macro or tool to compare two Excel spreadsheets

Topic

New Reply

Hi,

I was very impressed with the solutions suggested in this site, I came across this site just today. I need help. I have to compare 100 reports in excel format. The same report will be generated from 2 different environments and I need to compare both the versions to see if they are same or have any difference. I got a VBA macro from a friend which does the comparison but it doesn’t solve all my needs.

That macro does line by line comparison but im expecting the 2 reports will not have the data in the same order. Line 1 in report1 could be line 3 in report2 so I need a macro to do keyword based comparison.here is what I need, pls suggest if you know or have a macro for this purpose.

1. Compare all the files within a folder with the files in another folder
2. identify the record and then compare
3. update the results in the last column of the source file.

Thanks,
Prasanna

Reply | Quote

Replies

Surely there is a better solution than comparing spreadsheets. Can the reports be done another way, possibly fed into a simple database?

Are you able to give us a couple of sample sheets to work with?

cheers, Paul

Reply | Quote

Hi Paul,

I’m not sure if feeding the excel data in to a database will work because all the files are not in the same format, the 2 files we are trying to compare are of the same format but every set is in a different format. I have attached 2 sample formats. I don’t even know how many different layouts are there. If both the source and target files has the same data in the same rows then line by line comparison will work. My concern is what if there is an extra row in one of the files, I need the record based comparison in this case. Not sure what is the best way. Thanks for taking time to help me.

Thanks,
Prasanna

Reply | Quote

OK, this is probably going to draw some comments.

Open your Excel files in Corel WordPerfect. WordPerfect recognizes the spreadsheet as a table. Save in .wpd.

Then use the built-in redline feature in WordPerfect to generate your redline.

See attached. I compared the monthly to the daily. Easy peasy. The best built-in redline capacity is in WordPerfect.

Now, these are two radically different spreadsheets. Send me two files that you really want in redline and I’ll do it again.

Sincerely,

Randy K.
Fresno, CA

Hi Paul,

I’m not sure if feeding the excel data in to a database will work because all the files are not in the same format, the 2 files we are trying to compare are of the same format but every set is in a different format. I have attached 2 sample formats. I don’t even know how many different layouts are there. If both the source and target files has the same data in the same rows then line by line comparison will work. My concern is what if there is an extra row in one of the files, I need the record based comparison in this case. Not sure what is the best way. Thanks for taking time to help me.

Thanks,
Prasanna

Reply | Quote

I can’t see any correlation between those two sheets. Do you have 2 sheets that you would be comparing?

cheers, Paul

Reply | Quote

Sorry for the confusion, Paul. Attached 2 sample files to be compared. Thanks!

Reply | Quote

That’s a real apple n oranges report. There are several things to compare to work out if a value is different, unless you just count number of items.
What do you use to decide if an item is the same? Section A may use Group ID and Subgroup ID, Section B Group ID , Subgroup ID and Subscriber ID?
What do you update if you find a discrepancy?

cheers, Paul

Reply | Quote

prasannacts,

in addition to the questions Paul raises, please answer these:

Will the naming conventions be consistent as above between the files in one folder as compared to it’s sister file in the other folder?

Must all the cells in the record be identical to be considered a match or are there certain columns that may be considered insignificant in the comparison?

How do you want to handle the discrepancies? This could be as simple as placing an “X” in column L on both sheets next to the columns that have a match or could be as complicated as creating a separate sheet with all the unique listings that match and all the non matching records.

Maud

Reply | Quote

Hi Paul and Maud,

To identify the unique records, I will use group id, group name and sub group id in section A. In section B, group id and subscriber id can be used to find the unique records.

The file name will be exactly same for both the source and target files.

All the cells in the record should be identical to be considered a match.

For each row in both the files, I want the last column to be updated to indicate the result, either match or no match. No need to have a separate sheet.

Thanks again for taking your time to respond.

Prasanna

Reply | Quote

In the attached file, the macro requests a filename, opens the two files and does a comparison. To open the two samples you sent, just use “002_Daily” as the filename when requested. The macro appends the two suffixes for you.

It needs to be in the same folder as the files.

Hope this helps.

Reply | Quote

unclehewie,

Thanks for the macro, I tried it. It compares only 3 columns but for each record I need all the cells in the record should match between source and target. sorry if my previous posts were confusing. There are at least 200 reports I need to compare, all the reports are not in the same format, the number of columns could be different in each report.

for example, if a file has 100 rows and 5 columns, the macro should take row 1 from source file, search for that row through out the target file (the record might not be in the same row in both the files), if a match is found then update the last blank column in both the files as “match found” for that record. then take row 2 from source file and do the same process, then row 3 and so on until the last row. The last row will always be ***End of Report***. If there is no match found in the target file, then update “no match” for that record in the source file.

I have attached a macro I’m using currently. It does line by line comparison and also compares multiple files one by one. I need the following changes to be done in this macro, if you could help me, that will be great.

1. instead of comparing row 1 from source with row 1 from target, search for the record through out the target file because the same record will be in different rows in source and target.
2. currently the macro copies the source and target files and put them in a new file, 2 sheets within the same file, one for source and another for target, this is great. It highlights the differences in some color in the source sheet names “first1”. Apart from highlighting the differences, I also need the last column in the “first1” and “second1” sheets to be updated with match or no match as I explained in the above statement.

Thanks again for your help.

Prasanna

Reply | Quote

When I opened CompareMacros.xls and enabled the Macro, all spreadsheets were empty.

Reply | Quote

Jack – My output was generated in pdf. Redline file was uploaded as Excel compare.pdf.

Reply | Quote

I’ve amended your file so that it now does what I think you want it to do.

(I’ve added Option Explicit in the code as I like working that way, but you may want to remove that.)

Reply | Quote

To identify the unique records, I will use group id, group name and sub group id in section A. In section B, group id and subscriber id can be used to find the unique records.

Sounds like you can do a sort on each file by Group ID, to eliminate the keyword search problem.

You might try downloading and using the trial version of Beyond Compare…

http://www.scootersoftware.com/

Once sorted, Beyond Compare will show you which lines match or don’t–or you can do the sorting within BC. Works with Excel files.

Lugh.
~
Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

Reply | Quote

prassannacts,

If unclehewie’s nicely prepared spreadsheet does not fit your needs, consider this spreadsheet. The spreadsheet opens to a main screen where the user initially clicks on the source and target boxes to navigate and select the paths. This will need to be done only once if the paths do not change. The spreadsheet remembers.

42455-GetPath

Once the paths have been selected, the columns will populate with *.xls files in that folder. If new files have been added to the folders, the user can update the list by clicking the Update Files button. The user then selects one of the files in the source column. If there is not a matching file in the target folder, the user is notified.

42456-NoMatchingFile

If the user selects a source file that has a matching target file, the font will turn red for both files and the Compare button will now be enabled

42457-Matching-File

When the user clicks the compare button, the selected files are pulled into the spreadsheets as Source and Target worksheets and a bi-directional comparison is made. Matching records (“Match Found”) will be indicated in column M on both sheets. For source records that do not have a matching target record, “No match found will be listed in column M on the source sheet while any record in the target sheet that does not have a match on the Source sheet will have “No Match Found” in column M on that sheet.

Source:
42458-source

Target:
42459-Target

Going back to the Main sheet, selecting a new pair, and clicking the Compare button will import both new sheets and overwrite the Source and Target sheets. The original sheets will never be altered.

Created in Excel 2013, converted to 2003. There were no issues when tested in 2010.

HTH,
Maud

Reply | Quote