Hi There,
I found this thread that was very helpful, but I need some updates.
http://www.excelforum.com/excel-prog...n-a-third.html
The problem is that when a person has the same last name, the macro doesn't realize it is a different person. Can anyone assist?
Compare Test_Sample Data.xlsx
Thanks!
Last edited by EmilyFerrier; 12-07-2012 at 10:51 AM. Reason: FIXED
EmilyFerrier,
Thanks for the Private Messages.
You want to compare worksheet Old to worksheet New.
What are the results you are looking for?
Can you manually create a worksheet Results, and post the new workbook with the three worksheets?
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 EmilyFerrier,
I found that this will overcome the problem
If you add an extra field to each record that creates a unique ID by concatenating
Family Name Given Name Worksite
smith Mark LA
To create
smithMarkLA
goldsteinjohnNYC
johnsonemilyNYC
turnerwilliamLA
saltbrianLA
wongvanessaLA
wonghollyLA
wongmaureenNYC
I inserted the extra field at column B
@stanleydgromjr
I am looking for the same results that the macro you made provide, with the exception being to fix the last name issue.
@lima
That is an easy fix, but is there a way to fix it on the macro? I'd rather limit the amount of manual editing I need to do before being able to run the macro. The result set comes from a database export, so I'd like to be able to save the export, do minimal formatting to get the columns in the right places, and then run the macro.
EmilyFerrier,
You want to compare worksheet Old to worksheet New.
What are the results you are looking for?
The link that you provied to my macro contained two starting worksheets, and a third worksheet with the results that was created by the macro.
Please post a new workbook with the three worksheets:
worksheet Old
worksheet New
worksheet Results, manually created by you, with the results you are looking for.
If you can not supply another workbook with the three worksheets, then:
In the Quick Reply box, just put the word BUMP. Then, click on the Post Reply button, and someone else will assist you.
Compare Test_Sample Data.xlsx
Here is a workbook with my two sheets, and a results tab generated manually.
Thanks!
EmilyFerrier,
Thanks for the new workbook.
Worksheet Old contains two sets of raw data:
Range A1:N9 and Range A11:N21
Worksheet Old Range A11:N21 seems to be a duplicate of worksheet New Range A1:N11
Can I assume that worksheet Old Range A11:N21 can be removed?
Yes, you can delete Range A11:N21.
Thank you!
EmilyFerrier,
After examining your complete workbook, that is what I did to begin writing the new macro.Yes, you can delete Range A11:N21.
I have used column O in worksheets Old and New as a work area to combine in Upper Case the FristNameLastName.
My results are not quite like yours, yet.
What is your logic to get the results?
Are we working in worksheet New, to compare the FIRSTNAMELASTNAMEs, to what is in worksheet Old?
I will do what I can to answer your question, but the logic is....well, faulty.
Old is an export from last week from a personnel database. New is the same report run this week. The ultimate goal is that I need to mark each cell that has changed from the previous week, relative to the person, so straight location comparison doesn't work- it needs to connect to FIRSTNAMELASTNAME.
When I do this manually (keeping in mind it is uaually about 300 rows deep) I just make sure I am looking at the same person using Employee ID if is there, and the first name and last name. Then I go through each column to see where the information is different, and highlight that cell in the "New" worksheet. I liked that your macro made a summary page of the results found, but when i do it manually, I am not doing that.
If this doesn't help, please provide mroe specifics about what you need. Thank you so much for spending time on this!
EmilyFerrier,
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 CompareNewOld macro.
Hello,
I am in deserate need of a macro that will compare the same column in 2 separate worksheets and then copy in the second sheet only the cells that don't match.
I attached an example. Sheet 1 contains more rows than Sheet 2, and the macro should compare column B in both sheets and then write in Sheet 2 the cells from column B that are not found in the same column B in Sheet2 (in my example Sheet 3 is a representation of how Sheet 2 would look like after executing the macro).
I tried using the info already presented in the forum, but 1 day later I have no satisfactory result.
Can someone help?
Thank you a million!
N
nicoletanistor,
Thanks for the workbook.
Detach/open workbook UpdateSheet2 rngobjects colB - nicoletanistor - EF881339 - SDG15.xlsm, and, run the UpdateSheet2 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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsmPlease Login or Register to view this content.
Then run the UpdateSheet2 macro.
Thank you @stanleydgromjr for the solution.
Maybe I didn't explain very well, but the macro should check for each cell in column B from Sheet1 if it already exists in column B in Sheet2 and if it doesn't exist, then copy it at the end of what is written in column B in Sheet2. So basically it needs an extra verification if that field is already in Sheet 2. At the moment it copies everything from Sheet1.
Can you help with this?
Thank you again!
nicoletanistor,
Thanks for the feedback.Thank you @stanleydgromjr for the solution.
You are very welcome.
According to the three worksheets in your workbook, and, the instructions, and, the results in Sheet2 (the interior cell color), the macro does exactly what you requested?????
Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.
Indeed, it seems to work in the example I sent, but when I try to move it to my actual spreadsheet, currently with a few hundred rows, it copies everything from column B Sheet1 with the exception of the first cell.. I have no idea why it doesn't work with more amount of data
You are right, it works devinely! it was due to the fact that most of the cells in my actual file had a space after them, so that's why the macro copied all the fileds as being new, because the cells weren't matching exactly.
Thank you again for your help! much appreciated!
nicoletanistor,
Thanks for the feedback.
You are very welcome. Glad I could help.
And, 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