PDA

View Full Version : Compare two spreadsheets-Macro



Actuary1010
07-20-2011, 07:36 AM
Hello guys,

Thank you all for this wonderful forum!!! I've a problem that I believe it's not hard but I'm not quite sure how to tackle it. I would really appreciate and help and suggestion. Here it is:

The VBA code, attached with workbook, is used to compare two spreadsheets. The output of this code creates a new sheet that shows the differences have occurred, and if no difference, it leaves the cell blank.

I only need to compare the first 65 columns (up to column "BM").

I would like to obtain the following output in the new sheet after running the code:

1) Keep and copy the second row (variable name) into the new produced sheet (for the first 65 columns only).

2) Keep columns A, B, D, F, K, and L (I need them as identifying variables) and copy them into the new sheet (they are similar in both sheets).

3) for the two columns “Z” and “AA”, no need to do comparison since they will be always different, and I don’t want them to be in the new sheet.

4) Delete any row that has no changes.

In summary, I want a nice sheet that highlights the changes.

Thanks in advance, and I would really appreciate any help.

AA

rbrhodes
07-20-2011, 08:36 PM
Hi

not everyone has access to xlsm xls is still common!

Aussiebear
07-21-2011, 03:55 AM
Here you go Dusty;

rbrhodes
07-21-2011, 11:42 PM
Hi

Thanks AB.

Questions:

<<The output of this code creates a new sheet that shows the differences have occurred, and if no difference, it leaves the cell blank.>>

Leave a blank row?

<<1) Keep and copy the second row (variable name) into the new produced sheet (for the first 65 columns only). >>

What does this mean??? 'second row?

EDIT: Second row (variable name) is header! I get it! Literally second row...duh.

<<2) Keep columns A, B, D, F, K, and L (I need them as identifying variables) and copy them into the new sheet (they are similar in both sheets).>>

Does this mean no compare on these cols, just copy them? Does <<They are similar...>> mean they are the same?

EDIT: On all rows or just that ones that are being copied?

<<3) for the two columns “Z” and “AA”, no need to do comparison since they will be always different, and I don’t want them to be in the new sheet.>>

Delete Cols Z and AA from the new sheet or leave them in, (blank) so that all cols in all sheets are the same?

<<4) Delete any row that has no changes.>>

If row # is not identical in sheet 1 to row # in sheet 2 delete it, yes?

EDIT: Copy from Sheet 1 or Sheet 2 to new sheet?

That's it for now!

rbrhodes
07-22-2011, 03:33 AM
see attached example as per questions above

Actuary1010
07-29-2011, 07:26 AM
dr,

Thanks you so much for your help. Do you know how can I put the old and the new values in the cells of the new worksheet "Changed" .. something like old values-->new values?, because I need to see the changes. Also is it possible to delete all the blank rows that have no changes?

Thanks a ton for your help!!! You are the man!
A

Aussiebear
07-31-2011, 07:16 AM
Would this from the KB be useful to your concept?

http://www.vbaexpress.com/kb/getarticle.php?kb_id=1074

rbrhodes
08-08-2011, 03:55 PM
Hi,
Bordering on small project here... That said try this out.

Actuary1010
08-10-2011, 06:27 AM
Thank you so much dr. You're awesome!!

Best,

A


Hi,
Bordering on small project here... That said try this out.