is it possible to write a macro to compare two spreadsheets within excel and highlight the differences?

theNEOone

Diamond Member
Apr 22, 2001
5,745
3
81
i'm working with a lot of data that i have to manually compare at the end of each month. i'm working w/ a mortgage program that prints a report that lists the loans that we service. i take this list and compare it to an excel document, and manually highlight which loans have been added or removed, and then make the appropriate changes in the excel document.

previously, the mortgage report was handed to me, but i recently went into the program myself and realized that it is capable of exporting the list in excel format. i want to run a macro that will compare the imported spreadsheet to the previous month's spreadsheet and highlight the differences. both spreadsheets will have several columns, but for my purpose i only need to compare a single column in each document.

can this be done, and how? thanks.


=|
 

KLin

Lifer
Feb 29, 2000
29,500
125
106
do a vlookup from this months column to last months column, and have it return either YES if it matches to last month's loans, or NO if it doesn't, then filter against the vlookup column for NO. That would probably be the best way to do it.
 

theNEOone

Diamond Member
Apr 22, 2001
5,745
3
81
ok, i just realized that if i'm going to automate the comparison of the two worksheets, i might as well automate the entire process. although comparing the two documents is the most time consuming aspect of the process, i would benefit from automating the entire report.

in addition to comparing the column in the two worksheets (e.g. worksheet Bank1 and Bank2), i would like the macro to delete an entire row if the entry exists in Bank1 but not Bank2, but to copy the entry to Bank1 if it's in Bank2 but not Bank1.

i should add that i have never worked w/ visual basic or macros before, although i'm definitely willing to spend a considerable amount of time in learning how to fully utilize such tools. so although i would love some detailed instructions, i would also appreciate links to sites that give tutorials.


=|
 

theNEOone

Diamond Member
Apr 22, 2001
5,745
3
81
also, the columns are not continuous. that is, the column that i want to compare (say, column B) has information (such as totals, column titles, etc.) that i don't want the macro to compare. would it be possible to run the macro on cells in the column formatted only in a specific way?


=|