Update row or rows if unique value is found

  • Hi everyone I'm new here, I have searched everywhere for a solution but couldn't find one. In summary I have two workbooks, first one is the Tracker which is updated daily with new information and the second one is the Database where I store all the necessary information, both contain quite a lot of columns.

    I am trying to build a macro that would take the request ID (C2) in the Tracker and find it in the Database workbook (Column D). If a request ID already exists in the Database the macro would update the latest information from the Tracker. However, if the request is not in the database a new row or rows would be filled out with both the request ID and the associated information.

    I am using this code and have modified it to the intended purpose where it can create new entries by pasting the values (information) if request ID is not in the database. However as there can be 1, 2, 3 or 4 rows of data (a request may have up to 4 sub requests so it is broken down into 4 rows with the same request id), I am having trouble trying to get the code to find the row or rows where the request IDs are located and replace its associated information, for example this request ID ZX2211 has 3 rows, each row contains different information, does anyone know how to write the code such that it will go to the row where the request id is and then update all the values in the subsequent row or rows? Any help is very much appreciated!


  • You can't find unique IDs if there are multiple tows for IDs. Can't you use something like 1.1,1.2, etc.


    How many updates per day on approximately?

  • Dear Roy,


    Sorry for the late response, was so tied up with work.. the only way out is to create an additional column with the unique ID plus .1, .2, .3 etc?

    The database is updated on average 2-3 times per day.

  • Even having a column with the unique ID won't work. Unique IDs are just that unique. You cannot manage a Database with duplicate IDs.

  • Sorry, I mean for example if there are 4 sub requests under this ID SR20201210, I should use SR20201210-1 (1st row), SR20201210-2 (2nd row), SR20201210-3 (3rd row) and SR20201210-4 (4th row) as the unique IDs instead of just SR20201210 occupying the four rows, would that work?

  • I have attached both sample workbooks for your perusal. If there is a new request (Request No. SR20201210 with 4 different items) that comes in, when I press button 1, it will open up Database workbook to search if this request no. already exists in the database, if no, it will transfer the data from the Testing workbook to the Database workbook, if yes, it will just update certain columns, for example, Column B, C, E and F. Is it doable or do I have to create an extra column to make the request no. 'unique' like SR20201210-1, SR20201210-2, SR20201210-3, SR20201210-4?

    Files

    • Testing.xlsm

      (22.64 kB, downloaded 73 times, last: )
    • Database.xlsx

      (20.76 kB, downloaded 75 times, last: )
  • Both workbooks are .xlsx so no macros. I don't understand your reference to pressing a button.


    The "database" sheet is not in a format that is used as a database. There should be no completely empty rows or columns in the data table.

  • Hi Roy, hmm I don't understand, the Testing workbook I sent is in xlsm format, I created the button just to execute the macro, Database workbook is in xlsx format because it only needs to store information, no macro needed.

  • There is no macro for me to have a look at.


    The database workbook should not have empty columns and rows if you want to use it as a database.