Comparing 2 Spreadsheets

Hi, I need some advice on the best way to go about comparing two spreadsheets to create a difference report. A little background....

File Information

Spreadsheet 1 - list of program/file names and version numbers that developers submit in preparation for a software release.

Spreadsheet 2 - list of program/file names and version numbers that a software control program creates.

Both spreadsheets have an identical amount of columns and the are named the same. Each spreadsheet has a different quantity of rows. The primary information to compare on each spreadsheet is Program/File Name (column A) and Version Number (column B).

Puprose of this Task

Sometimes the software control tool includes program files in error and those need to be identified for review. This would be the cross match on columns A and B from spreadsheet 1 to 2.

Conversly, the software control tool (or the person doing it) misses program files, which also need to be identified. This is the cross match on columns A and B from spreadsheet 2 to 1.

Desired Output

I'm hoping to come up with an automated solution since each of these files can have upward of 2000 rows. We manually review now and it's extremely time consuming. What I hope to do is have a solution the generates a third spreadsheet (Difference Report) the shows the following -

1. The following rows from spreadsheet 1 were not on spreadsheet 2.
2. The following rows from spreadsheet 2 were not on spreadsheet 1.

I'm hoping for input on whether or not I can do this with Excel.. would it be a function/formula? A macro? VBA? Abandon Excel and use Access?

Thanks for your help. If anyone has the time to work a solution for this, I do have a some data to play with.

You can get some ideas here. You might want to create a column on each that
concatenates the program/file and the version.

http://www.cpearson.com/excel/duplicat.htm

--
Regards,
Tom Ogilvy

"mizzrizz" <[email protected]> wrote in
message news:[email protected]...
>
> Hi, I need some advice on the best way to go about comparing two
> spreadsheets to create a difference report. A little background....
>
> _FILE_INFORMATION_
>
> Spreadsheet 1 - list of program/file names and version numbers that
> developers submit in preparation for a software release.
>
> Spreadsheet 2 - list of program/file names and version numbers that a
> software control program creates.
>
> Both spreadsheets have an identical amount of columns and the are named
> the same. Each spreadsheet has a different quantity of rows. The primary
> information to compare on each spreadsheet is Program/File Name (column
> A) and Version Number (column B).
>
> _PUPROSE_OF_THIS_TASK__
>
> Sometimes the software control tool includes program files in error and
> those need to be identified for review. This would be the cross match on
> columns A and B from spreadsheet 1 to 2.
>
> Conversly, the software control tool (or the person doing it) misses
> program files, which also need to be identified. This is the cross
> match on columns A and B from spreadsheet 2 to 1.
>
> _DESIRED_OUTPUT_
>
> I'm hoping to come up with an automated solution since each of these
> files can have upward of 2000 rows. We manually review now and it's
> extremely time consuming. What I hope to do is have a solution the
> generates a third spreadsheet (Difference Report) the shows the
> following -
>
> 1. The following rows from spreadsheet 1 were not on spreadsheet 2.
> 2. The following rows from spreadsheet 2 were not on spreadsheet 1.
>
> I'm hoping for input on whether or not I can do this with Excel.. would
> it be a function/formula? A macro? VBA? Abandon Excel and use
> Access?
>
> Thanks for your help. If anyone has the time to work a solution for
> this, I do have a some data to play with.
>
>
> --
> mizzrizz
> ------------------------------------------------------------------------
> mizzrizz's Profile:
http://www.excelforum.com/member.php...o&userid=35515
> View this thread: http://www.excelforum.com/showthread...hreadid=552938
>

Thanks, Tom. Looks like there are some good nuggets on that link that you sent. I will check it out!