Compare two worksheets and present differences in a third

Hi all, I have been spending many hours looking for some code to do some relatively simple comparison task but I haven't been able to find an answer to my particular problem.

I have 2 worksheets "today" and "yesterday", each with the same number of columns but potentially different number of rows (each row is a different project). The structure of each sheet is:

NAME // IDNUMBER // COST YEAR1 // COST YEAR2 // MANY OTHER COLUMNS
aa // ABT-100 // 100 // 800 // etc...
bb // SRT-876 // 245 // 445 // etc...
and so on... (please note that the IDNUMBER has a string of letters and numbers separated by highphens).

I am looking for some VBA code to compare "yesterday" and "today" worksheets for any changes by ID NUMBER and paste any item that has changed in a third worksheet "changes" with some highlighting:

1) any item (IDNUMBER) that is in "yesterday" but not in "today" should be copied in the worksheet "changes" with the whole row highlighted in red (dropped item).
2) any item (IDNUMBER) that is not in "yesterday" but that is in "today" should be copied in the worksheet "changes" with the whole row highlighted in green (new item).
3) any item (IDNUMBER) that is both in "yesterday" and "today" should only be copied in the worksheet "changes" when some cell of the row has been modified (i.e. difference between "yesterday" and "today"), in which case, the modified cells should be highlighted in yellow. Ideally, when some changes occured for an item, I would like to be able to present both values in the same cell next to each other ("yesterday",s value in red and "today"'s value in green).
4) do nothing with items that haven't changed between "yesterday" and "today".

I am guessing that this would probably involve some pretty intense coding so efficiency of the code is probably important here.

I have tried my best to do this but just can't do it!

Please!!!! Can someone help me!!!

Many thanks.

olivierpbeland,

Welcome to the Excel Forum.


Should the ID GAEY-05-2012-v1, in both sheets Yesterday and Today, contain the same Name?


If NOT, which name should we use?


You have:

Worksheet Yesterday:
Name 11, GAEY-05-2012-v1, 7, 7, 7, 7, 7, 35

Worksheet Today:
Name 9, GAEY-05-2012-v1, 8, 9, 10, 11, 12, 50


Worksheet Changes:
Name 9, GAEY-05-2012-v1, 7/8, 7/9, 7/10, 7/11, 7/12, 35/50

olivierpbeland,


Detach/open workbook CompareYesterdayToday sheets to Changes - olivierpbeland - EF785472 - SDG15.xlsm and run macro CompareYesterdayToday.


If you want to use the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the CompareYesterdayToday macro.

Thanks for the update. If it works then please show the thread as "Solved" and if your satisfied with my help please check the "Scale"

olivierpbeland,

Its late and am going off line. If you do not have a diff. answer, I answer on tuesday. I'll be gone on monday.


Disreguard my post on this thread.

olivierpbeland,

Could you give directions to make it open endend column-wise?

Can we have another workbook with openended columns for testing?

olivierpbeland,

The code works from B2 to the last used cell in column B.

I assume that there are no blank ID's in column B in worksheets Yesterday and Today.

Please Login or Register  to view this content.

olivierpbeland,

Per your Private Message:

Today, 08:27 AM
olivierpbeland
Registered User Join Date: 24 Jul 2011
Location: Ottawa
MS Office Version:Excel 2003
Posts: 5
Re: Compare worksheets copy differences in a third
--------------------------------------------------------------------------------
Thanks Stan but I'm not sure I understand. For the moment, the column structure will be as in the workbook you worked on. However, it is likely that we will have to add (and maybe even remove) columns during the project and I had hoped that the code could be made flexible enough to
1) determine the number of columns
2) run the code you sent me with the appropriate number of columns.
As such, I am not too sure to know what to send you...Could you be more specific please.
Many thanks for your kind help!
Olivier

For any single project, will worksheets Yesterday and Today always have the same number of columns for that project?

Please attach another workbook with a different number of of columns for testing?

olivierpbeland,


I have tested the new macro by deleting the same columns in both worksheets, and by adding the same columns in both worksheets.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the CompareYesterdayTodayV2 macro.

nikol_28,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.