VBA if value exists, update values in relevant columns, else add value and relevant data

    Hi,


    Fairly inexperienced with VBA, and am trying to setup a workbook that I can paste a data set into one sheet (update data), then run some VBA to check a different sheet (data insert test) for if a key exists (notification) and insert the data there to update it or add the new key(s) at the end of the data. Below is the general flow of what I'm trying to do, as well as an attached workbook with how far I've gone. Could be using the wrong approach with my code, but if anyone could help me out that would be great, thanks.


    If (notification) exists in "data insert test"

    then update (order,test1,test2)

    Else insert (notification,order,test1,test2) in last empty row

    Your sample file has no data in the "data insert test" sheet so there is no data to search. It would help if you could post a revised file with the "data insert test" sheet populated with data.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    Currently the code I have in there pastes the data from the "update data" sheet, to the "data insert test" sheet, which is what I want it to do. So what it does right now is you hit the refresh data button, and since there isn't anything on the "data insert test" sheet, it pastes everything in there, and it won't paste duplicates there which is good. So now I just need it to be able to update existing values with what i change in "update data" sheet, if that makes sense.

    Hello,


    What you are trying to achieve looks rather simple ...


    However, you seem to be over-complicating things ...


    Currently your Data is copied from ' update data ' to ' data insert test ' ... for Unique Notification numbers ...

    ( 116733247 is only copied once ...)


    As asked by Mumps ;) , could you include in your test file an illustration of your final expected result ...


    P.S.  Macros should be stored in standard modules ... Sheet modules are reserved for Event Macros ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Thanks for the responses, here's the full workbook I'm looking to do this code in. The process I'll be doing is taking data and pasting it to the "Update Data" sheet. After putting the data there, I need the code to look at the "Weekly Data" sheet and for every notification in the "Update Data" sheet that already exists, update column J and K. For every notification that doesn't exist, paste that notification and subsequent data to the last row in the "Weekly Data" sheet. Let me know if this makes more sense, thanks.

    Thanks for the sample workbook


    Sincerely hope Mumps, who is an outstanding ' decryptor ' , will be able to help you out ...


    Personally, I do not understand the final outcome you are expecting :S

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hm guess I'm not explaining very well. Basically I just need this workbook to be a running tracker for scheduled and complete jobs. So when new jobs come in, the job and its corresponding data (schedule date) are added after the last row in the "weekly data" sheet. Existing jobs will be updated from time to time as they are completed, so I'll want the existing jobs in the "weekly data" sheet to be found and then update the complete date column.


    To make this easier for me so I don't have to manually do it (hundreds of jobs come in or need to be updated), I wanted the code to execute both of the above at the same time as new jobs could be mixed in with complete jobs in the update data that I'm pasting to the "update data" sheet. Does this help at all?

    Just to clarify:

    -As new jobs come in, they are added at the bottom of the Weekly Data sheet. Each job will have a Contractor Schedule Date in column J.

    -As the jobs are completed, you will enter a completion date in column K.

    -When the completion date is added in column K of the Weekly Data sheet, you want to find that job in the Update Data sheet and insert the completion date for that job.

    Is the above all correct? If so, are the Notification and Order numbers unique to a particular job? In other words, there are no duplicate numbers. In which column in the Update Data sheet do you want the completion date? It would help if you had column headers in the Update Data sheet.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    This is sort of correct, but I want to paste new data to the "Update Data Sheet", and update the "weekly data" sheet with the data from the update data sheet. I will not be putting in completion or schedule dates manually, those will just be part of the data that is pasted to the "update data sheet".


    So with new jobs that I paste to the "update data sheet", they would be automatically added at the bottom of the "weekly data" sheet, and they would have a schedule date. In very rare cases Notification and Order can sometimes duplicate, however the combination of the 2 will always be unique. Existing jobs would just have complete date automatically inserted on that jobs row, moving the complete date for the job from "update data" sheet to "weekly data" sheet. None of the data in the "update data sheet" will be updated via code, that is the data I want to use to update what is in the "weekly data sheet".

    Let me try again.

    -You want to take all the data you paste into the "Update Data" sheet and copy it to the bottom of the "Weekly Data" sheet.

    -This data will have a schedule date but not a completion date.

    -Jobs would have the completion date entered on the "Update Data" sheet at a later date when the jobs are completed.

    -When the completion date is added, you want that date copied to the "Weekly Data" sheet for that job.


    If this is all correct, is the completion date entered manually in the "Update Data" sheet?


    If I am not understanding correctly, try to be very specific in describing step by step what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    So what you have there is exactly what needs to happen, although the only thing is the data will have a schedule date but could also have a complete date, both will just be pasted in on a weekly cadence. I won't be going back and updating the data in the update data sheet manually, I'll just be overwriting what is there each week.

    Usually the case will be the data has a schedule date, then a week later or however long I'll have data that has the schedule date along with a complete date. So workflow would be as follows:

    -Receive a workbook with job data, copy this data and paste to weekly tracker in the "update data" sheet

    -Once data is pasted to update data sheet, press the Insert Data button to run the VBA code

    -VBA runs: IF (new job) THEN insert at the bottom of the weekly data sheet; ELSEIF (job exists) update complete date from update data to weekly data


    Thanks for working through this with me, in my head this seemed a bit more straight forward haha.

    Let's give this a try and see what happens.

    Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Update Data sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Click the Insert Data button.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    Love 1

    Welp this seems to work perfectly, first try. Reading through the code, most of it makes sense to me, think the only thing I'm not used to seeing is the CreateObject("Scripting.Dictionary") line, but otherwise it looks like a loop for 9 columns is that right?


    Thanks a bunch for putting this together for me.

    You are very welcome. :) It's similar to a loop but the macro uses arrays. It loads ranges from the 2 sheets into arrays and then deals with the data in memory rather than having to go back and forth accessing the data in the actual sheets. Using arrays is very fast especially if you have a very large data set. The dictionary simply stores all the data in memory. I hope that helps.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    Love 1

    Yup that all makes sense. Does this method only work if both arrays are the same size? For instance could the sheet that's being updated have more columns than the sheet it's being updated from? I'm thinking the answer would be no as it wouldn't know which column to place the values in, but am interested in making that work as I could adapt this method for a lot of scenarios if it were to work with different sized arrays.

    Both arrays can have different sizes. You can resize the arrays by the number of columns you want to use and then refer to the columns using the column index number. For example, v2(i, 4) would refer to the array v2, the "i" is the counter in the loop and the "4" is the fourth column in the array (not the fourth column in the sheet which is column D).

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

    Love 1

    Haha I actually ended up testing and it did work, and reading through your explanation here that is what I discovered through testing. Appreciate all the help and continued help on this!

    I have returned, wouldn't bring up an old thread but it's on topic, just was wondering how I would get this to work to replace values for duplicate notifications? So if there were 2 notifications of 1234567890, and i wanted both of them to have the same value for X, what would need to be altered in the code? Let me know if that makes sense, thanks.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login