Solved

Excel Sheet Comparison Macros

Posted on 2016-08-02
16
50 Views
Last Modified: 2016-08-04
Hello,

I need to compare data between two sheets in one workbook. The worksheets has columns with different headings including: Promise Date,Planned ShipDate, Account Item, Item No... etc. The list has over 1000 rows. Every week, the list is updated: old orders are removed, and new orders are added. I'm planning on having the previous week's order log on Sheet1 and the current week's order log on Sheet2. I'm looking for a macro that would highlight differences by cell, but not by location. For example, the 4th column (Sales Order Number) would be the fixed variable in each row, and the macros would recognize if values in the same row had changed. Additionally, if an order appears on the old list, but not on the new, it would be deleted. I've attached a file to show the example of the column headings.

I'm very new at Macros, so I'd highly appreciate someone walking me through this.

Thank You!!
0
Comment
Question by:Member_2_7970183
  • 7
  • 7
16 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
Sorry, no file attached in your original post.

It might be possible to detect differences in each order without macros.  But if you want old orders removed automatically, that would require some automation.

I'll take a look at your file once you post it.

-Glenn
0
 
LVL 17

Expert Comment

by:xtermie
Comment Utility
To start with, you could consider having a column in Sheet1 (old) that would lookup the unique Sales Order Number (col4 in both sheets?) in Sheet2 (new)  and vice versa.
However, note that if an Sales Order Number is not in the same position, it does not necessarily mean that is not in the worksheet.  However, if a Sales Order Number appears on the old list (Sheet1), but not on the new (Sheet2), using the above lookup check, can be easily deleted via a macro.

Could you send a sample file?  There was no sample file attached to your original question :)

Thanks
0
 

Author Comment

by:Member_2_7970183
Comment Utility
Hi guys,

Thank you for your responses! I've attached a file as an example. You can see that Sales Orders 1930582 and 1860592 are not shown in 'Sheet 2' because they are orders that have been completed, and thus pushed out of the order log. Additionally, new orders are added in 'Sheet 2' and some of the dates of orders that appear on 'Sheet 1' are different on 'Sheet 2'. So, everything is fixed by the order number. So showing changes that occur in cell A2 doesn't help me because the sheet is constantly being changed.

I'd like to have any changes in values (of orders that appear on both sheets) to be highlighted. For example, Sales Order 42876. The planned ship date changes from 8/3 to 8/7. Additionally, I would like the complete rows of any new sales orders added in 'Sheet 2' to be highlighted.

I misspoke when I said that I want old orders to be removed; they are already removed. I just don't need anything highlighted to tell me that old sales orders are gone.

I hope this makes some sense. I really appreciate you guys helping me!

Thank you!!!
Mexample.xlsx
0
 
LVL 17

Expert Comment

by:xtermie
Comment Utility
Check the example with the macro
Any differences in the NEW sheet from the old is highlighted in RED FONT.
If this is what you want, a similar approach can highlight new sales orders added in 'Sheet 2'.

Let me know!

Cheers.
Mexample_wmacro.xlsm
0
 

Author Comment

by:Member_2_7970183
Comment Utility
Xtermine,

This is exactly what I'm looking for. Would the macro be able to do this for all the rows of data? I noticed that the second item (Sales Order 42876) had changes but wasn't highlighted. Also, would there be a way to combine this functionality with new sales orders that are put in?

Thank you so much!
0
 
LVL 17

Expert Comment

by:xtermie
Comment Utility
Great!
Yes it will do it for all rows used in the sheets
I will recheck the macro later on today and will work on the highlighted NEW records in Sheet 2 part and get back to you asap (its 8 pm here so it seems you will getting your full answer tomorrow :) )
0
 

Author Comment

by:Member_2_7970183
Comment Utility
Take as long as you need! Thank you very much, you've really helped me out!
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 17

Expert Comment

by:xtermie
Comment Utility
See new attachment with revised macro that will:
Highlight old records, with yellow fill (in new sheet)
Highlight new order numbers with green fill (in new sheet)
Apply red font color in differences of old record (In new sheet).

I think that was what you wanted to accomplish, correct?
I have run the macro and Sheet2 is formatted.

Let me know!
Mexample_wmacro_v2.xlsm
0
 

Author Comment

by:Member_2_7970183
Comment Utility
Xtermie,

Yes this is exactly what I am looking for, there is only one slight issue. Some Sales Orders have more than one product associated with them. I've attached a file that shows an example: There are now two separate rows associated with Sales Order # 998763. Once the Macros is run, it highlights in red the other item associated with that SO#. Is there a way to fix that? Also, the yellow highlight for orders that appear in both isn't needed, just the green for new sales orders, and the red if anything in old sales orders have changed.

I hope I'm making some sort of sense here. I really do appreciate your help!

Thank You!
New_example.xlsm
0
 
LVL 17

Expert Comment

by:xtermie
Comment Utility
Hey...the yellow part is easy to eliminate, check my new example.
For the two records with the same SO# it may need a bit tweaking, as the macro was built based on the assumption that the SO# was unique.  Probably what I could do is check the combination of SO# and Product (which should be unique) and try to fix it as you want.
Mexample_wmacro_v3.xlsm
0
 
LVL 17

Expert Comment

by:xtermie
Comment Utility
Hey...did some changes, and I think now it is exactly as you want it.
Red font for changes, beware of item and SO#, and green fill for new records.
LMK :)
New_example_xtermie.xlsm
0
 
LVL 17

Accepted Solution

by:
xtermie earned 500 total points
Comment Utility
Not sure what you mean, but as per your example, product/SO#  should be unique as if there was the same product in the same SO it would be one row, right? and the same SO# could not be reused again so it only comes with multiple products.  Did you check the marcro ?  It works as you wanted I guess :)
You are welcome :)

PS.  If you are happy with the result, don't forget to accept my solution - the last comment (I see you are new here...)
0
 

Author Comment

by:Member_2_7970183
Comment Utility
Thank You so much Xtermie!! It works exactly the way I would like it too. On my Excel sheet of over 500 rows, there is just one row that seems to have red highlights for no reason. All of the contents of the row appear exactly the same as on the prior spreadsheet. Any idea what might cause this or how I could fix this?

Thank you again! I am accepting your solution right now!
0
 

Author Closing Comment

by:Member_2_7970183
Comment Utility
VERY VERY VERY HELPFUL!! Had a specific problem and this user was able to help me tackle it with ease!
0
 

Author Comment

by:Member_2_7970183
Comment Utility
Xtermie,

Would there be a way to highlight cells in yellow that are changed to empty after the Macro runs? For instance if there was a lot number for a product in the old product data and it changes to blank in the new product data, could that be something that is highlighted in a different color??

After this I think it will be totally complete.

I thank you x10000 for helping me over the last couple days. Your knowledge has saved me countless hours of work.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now