Hello,
Hope everyone is having a good day.
I'm hoping for some guidance on how to best analyze differences between 2 sheets in a workbook. I have tried some conditional formatting and it doesn't seem to be working correctly.
I would like to analyze what customers are on the inception sheet, that are not on the 2nd sheet, june11_current.
Any assistance would be appreciated.
JTPhillips,
We can use a macro to:
1. create a list on a new worksheet Results?
2. highlite the name on the inception sheet if it is not on the current sheet? If so, what background color?
3. use a column on the inception sheet, title Not In Current, and put in the cell NOT? You could then filter on column G?
What results are you looking for?
Have a great day,
Stan
Windows 10, Excel 2007, on a PC.
If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
Hi,
Thanks for taking a look at this. Any of these would be great. I think I would like to learn how to do #2, and highlight in red.
Hi JTPhillips,
This should put a Tilde in Column G if there is no counterpart on the other sheet - but I didn't find any!
=IF(LOOKUP(A2,June2011_current!A:A)<>"","","~")
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Sorry, formula giving incorrect results
Last edited by WasWodge; 06-13-2012 at 08:59 PM.
If my solution worked (or not) please let me know. If your question is answered then please remember to mark it solved
Computers are like air conditioners. They work fine until you start opening windows. ~Author Unknown
JTPhillips,
There are two hidden worksheets, Sheet2, and Sheet3, in the workbook?
Are these sheets being used by other macros?
Hi Stan,
I don't know why they were there, but I see them now.
I do have a couple of macros saved in my personal macro workbook, and it looks like they were using one of these, again how I don't know but they don't need to be there for any reason. They can be unhid and deleted.
Sorry...I must have have done something in my self teaching of macros. Thanks
Corrected formula in conditional formatting (as far as I can see), forgot i needed a named range
Hi,
I am so grateful for your help. I'm taking a look now. I's so appreciative of everyone's help. I'm home with a terribly broken leg and having so much difficulty thinking work wise,
What was the formula you used?
TESTME is a named range on sheet "june11_current" from A2 to A4007, you need to use a named range if using a different sheet (which is what I neglected on the first attempt)=COUNTIF(TESTME,A2)<1
JTPhillips,
Detach/open workbook Compare2Sheets - w1 w2 - JTPhillips - EF838119 - SDG10.xlsm and run the Compare2Sheets macro.
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 run the macro on another workbook, make sure that there are no hidden worksheets. And, that the left most worksheet is inception_5.31.11, or its equivalent, and that the workbook to its right is June2011_current, or its equivalent.
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 Compare2Sheets macro.
Dear Stan,
Good Morning. I want to thank you so much for taking your time on this. I am getting settled in for the morning and I'm going to give this a go. I'll let you know what happens!
Dear Stan,
This worked beautifully. I had several other spreadsheets to compare today and they all came out perfect.
Thank you so much. You can take a look at our website and see how your efforts helped a great cause www.travismanion.com.
I am trying now to marie the the thread as solved.
Fondly,
Jill
JTPhillips,
Thanks for the feedback.
You are very welcome. Glad I could help.
Come back anytime.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks