Solved

Excel Sheet Comparison Macros

Posted on 2016-08-02
16
57 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
ID: 41740150
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
ID: 41740353
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
ID: 41740567
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
ID: 41740745
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
ID: 41740949
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
ID: 41740992
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
ID: 41740998
Take as long as you need! Thank you very much, you've really helped me out!
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 17

Expert Comment

by:xtermie
ID: 41741884
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
ID: 41742599
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
ID: 41742716
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
ID: 41742888
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
ID: 41742903
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
ID: 41743053
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
ID: 41743057
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
ID: 41743221
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
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: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

919 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

14 Experts available now in Live!

Get 1:1 Help Now