I have a spreedsheet (SH1) that I'd like to compare Column C against spreadsheet 2 (SH2) Column A. Whenever I find a match, I'd like to update column L of SH2 with the contents of column K of SH1. When I don't find a match, I'd like to add the contents of Column C from SH1, as a new entry to Column A in SH2. Can this be done easily? I've been trying to write macros with no luck at all. Thx
If you would like to get a reliable solution it is best to upload a sample workbook with no sensitive data showing how each workbook is set out and also showing the end result - please be very specific explaining the logic and show in an example sheet how the workbook looks prior to the code running and after the code runs how the result should appear. Without seeing the layout it is difficult to write a solution that will work for you. Make sure there is no sensitive/private data in the sample workbooks you upload. To attach the sample workbooks select Go Advanced, Manage Attachments and select the workbooks to upload. Welcome to Excel Forum.
Hope this helps.
Anthony
Pack my box with five dozen liquor jugs
PS: Remember to mark your questions as Solved once you are satisfied. Please rate the answer(s) by selecting the Star in the lower left next to the Triangle. It is appreciated?
Enclosed are two files. If any cell in Column C, of DailyStatus, matches any Cell in Column A of the BudgetUpdate then I want to copy Column L of the DailyStatus to Column K of the BudgetUpdate. If there is no match, then I want to add the number from Column C of DailyStatus to the end of Column A of the BudgetUpdate. These spreadsheets may have 1000 or more line items. Thanks
You have 2 sheets in the Sample Budget update workbook - one is called Daily Status. When you sayandI want to copy Column L of the DailyStatus to Column K of the BudgetUpdateyou are talking copying data between workbooks and you are not referring to the Daily Status worksheet in the Sample Budget Workbook. Should there be a Daily Status Sheet in the Sample Budget workbook or did you just place it there for my convenience? I am assuming you want data copied between workbooks if there are matches and when there are no matches and the Daily Status worksheet in the Sample Budget workbook has no relevance to what you want the code to do. Am I correct in my assumption?If there is no match, then I want to add the number from Column C of DailyStatus to the end of Column A of the BudgetUpdate.
Yes, I put it there for convenience. I want to compare between two workbooks. I keep a budget update and I get daily status reports. If the data has changed in the status report I want to copy the actual hours to the budget update, if there is no match then I want to add that # to the last row of the budget report since this will be a new effort that I previously did not capture.
Thanks
This should do what you require - I have placed a button on the Daily Status sheet of the SampleDailyStatus workbook - it will first ask you to browse to open the Budget workbook file so you only need to start with the Daily Status workbook open. Once the Budget workbook is open it will search column C of the Daily Status sheet and copy the data as per your instructions. Before you run this on any real data ensure it does what you require and make sure to back up any essential data as you cannot undo the changes made by VBA code. Remember you do not have to have the Budget Workbook open - you only need to have the Daily Status workbook open to run the code as part of the code asks you to browse to the Budget workbook.
If there are any problems or any corrections needed let me know. I have attached the workbook and code is as follows. You can use the Sample Budget workbook you sent me to test against the attached workbook as the Budget workbook does not contain any code.
Please Login or Register to view this content.
Thanks. It appears to work. I need to change it so the macro is for the BudgetUpdate because that is the constant and a new DailyStatus gets sent in every day. I also need to do an insert new row before adding new numbers to the end of the BudgetUpdate spreadsheet since it contains totals for some of the columns. Other than that it seems to work great!!! I spent days on it, (first time I attempted to write macros) and got absolutely nowhere. I will need to take some courses. Thank You!!!!
Do you want me to make those changes or are you ok to amend the code yourself?
If you don't mind that would be GREAT!!! I will try on my own as well because this is something I would love to learn. THANK YOU!!!!
Here is the amended code for you. Just check this is what you require.
That is GREAT!! I appreciate it TONS!!!!! THANKS
I am glad it worked. If you are happy the problem has been finalised can you please change the thread to solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.
@ stashu117
Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.
Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Thank You. I thought I had marked this as solved.
In any case, the worksheets have changed and I need more help. I now need to compare column A in the Daily Status with column A in the BudgetUpdate, if there is a match I need to copy the contents of M (DailyStatus) to column K (BudgetUpdate). If there is no match, I need to add a row at the end of BudgetUpdate and copy column A to A, B to B, C to C, D to D, J to J and M to K. Thank You. Please let me know if this needs to opened as a new issue.
I went back to the original workbook and had a look to refresh my memory - with what you describe it would be best if you could please supply the 2 sample sheets like you did previously as obviously the layout has changed a bit. Column A in budget update used to be a # Identifier however in Daily Status it was just Approved/Not Approved - It would be much better if I could see the data layout so I know what sort of data I am comparing - please upload sample workbooks and if you think you need to give any more explanation please be specific in what is required e.g. If there is a match/no match. Once you upload the sample workbooks I will rewrite the code for you
I have a spreadsheet "Budget Update" that I want to update on a daily basis with the contents of the "Daily Status" worksheet. I want to search Daily Status and if a number in column A matches a number in column A on the Budget Update, I want to update Column D and J of the Budget Update with the contents of D and J from the Daily Status. If the number is not found, I want to add that number to the end of column A on the Budget Update and copy the contents of B, C, D and J and copy the contents of M from the Daily Status to K of the Budget Update. Thanks for all the help!!!
As I was trying to do this I realized I made a mistake in my explanation:
I have a spreadsheet "Budget Update" that I want to update on a daily basis with the contents of the "Daily Status" worksheet. I want to search Daily Status and if a number in column A matches a number in column A on the Budget Update, I want to update Column D and K, K equates to column M of the Daily Status of the Budget Update with the contents of D and J from the Daily Status. If the number is not found, I want to add that number to the end of column A on the Budget Update and copy the contents of B, C, D and J and copy the contents of M from the Daily Status to K of the Budget Update. Thanks for all the help!!!
I will have to update my code - I was just about to upload it - will make changes and upload soon for you.
I am a bit confused so can you confirm
1. Search Daily Status workbook and if Number (Column A) from Budget Update is found in Number in Daily Status (Column A) then copy Column D from Daily Status to Column D of Budget Update, Column M from Daily Status to Column K of Budget Update, Column J of Daily Status to Column J of Budget Update.
2. If number is NOT found then add the all the Numbers from Daily Status to Budget Update and Add corresponding values from Column B,C,D, and J from Daily Status to Budget Update and Column M from Daily Status to Column K of Budget Update.
3. Move the totals in J-N of Budget update to last row.
Have I got that right?
That is correct. Thanks
Try the attached workbook - I have attached the code to a button for you. Let me know if the rules are correct.
GREAT A+ Job!!! Greatlt Appreciated
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks