Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Sheet Comparison Macros

Posted on 2016-08-02
16
Medium Priority
?
82 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 18

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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 18

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 18

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
 
LVL 18

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 18

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 18

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 18

Accepted Solution

by:
xtermie earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 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