Find value and overwrite existing value if found else create new record Column 2 row

Hi guys, I consider myself a total n00b on excel and this is pretty much my first post here. I appreciate your help.

I am trying to create a tool that updates a database (arranged in rows) in Sheet 1 with the inputs from the column(s) in Sheet 2. The most important thing is that it should take a key indicator from the inputs and compare in the database if the key indicator is already there. if it is, then it should overwrite the values that changed (this to avoid duplicates) and if it's not found in the database, then it should be added in the last row.

I found a macro in a similar thread and it seems to do the magic when the input and output are rows and the input starts from A1. The problem is that the tool's input I'm working with has been arranged in columns and is placed around referenced cells and flags, and I don't know how to twitch the macro to pull info from the column (or columns If i wanted to make a multiple entry) into rows.

Attached you'll find a very basic analog version of the worksheet I'm trying to make work and this is the code that seems to be really close to what I'm aiming for:

Please Login or Register  to view this content.

Update DB.xls

Are the cell references in your example workbook accurate to your real workbook? In other words, will the Primary Keys, on Sheet1, all be in row 4 starting at column D and increase by one column each key.

Just for clarification the data on Sheet1 will be replacing the data on Sheet2?

Hi stnkynts,

Actually the primary key would be starting at E6 and just having column E to add/overwrite a record in the database would do but it wouldn't hurt if on top of that I have the possibility of having a second or third column to add new records
to the database as it does if I place it in rows.

The original file has a column with a dropdown box that looks for existing records and if it finds it it displays it in column D, the user still has to copy paste and modify values in column E before updating the database. Anyway here I'd need to think of something but this comes later.

I attached a simplified file similar to the original.

Update DB 2.xls

So the primary keys in row 6 starting at column E should be searched for in column A on Sheet2. If found replace the found row data with the corresponding data on Sheet1 from the searched column?

Yes, in the complex version that would be the goal. But I'd be pleased if I had a one column, you know the very basic version I posted initially.

It is easy to code for multiple columns vs one column. The hard part is just making sure I understand what you need. Give this a try and let me know.

Please Login or Register  to view this content.

Absolutely awesome stnkynts you're a rockstar! It does way more than I was actually expecting. It would have talken me like years just to tweak something halfways as good as this one.

Thank you, I appreciate it a lot.

Glad I could help and thanks for the rep.

Sheet 1 on your Update DB 3 file looks completely different than your Update DB 2 file.

What I’d like to do is to add the capability of allowing the user to overwrite the contents in column D once an existing record has been fetched based on "Primary Key" D6, update the database and reset the fetching formulas after the database has been updated.

I don't understand what you want.

I guess the best would be to reset the index match formulas on column D everytime an existing record in D6 is chosen. Do you have a better suggestion?

(Assuming Update DB 2.xls)
Even more confused. Cell D6 contains a data validation which references a named range on Sheet2. If you are wanting to have the Data Validation include all Primary Keys in Sheet2 you can modify the code I supplied to update the ReferredTo range of the Named Range.

Please Login or Register  to view this content.

I'm so clumsy, I uploaded the wrong file

Yes, we can assume UPDATE DB 2.xls

Maybe I can explain myself better by explaining the steps the user should follow to update the database:

First the user should check in D6 if the Project "Primary Key" already exists and if it doesn't exist the user should then go to column E "New Input" and fill in the fields to update that new record into the database in Sheet2.

The difficult part is when the record exists.

If the record exists then then D7:D13 will fetch the values from the database in Sheet2. The user then should be able to modify the cells that have to be changed directly into column D, run the macro and then repeat the process from the first step.

Right now if any value in D7:D13 is modified then the formulas will change to the new values and if we try to fetch information from the database it won't work. Therefore I'd like to add something to the macro so that once the user modifies the cells and runs the macro, then the macro resets the formulas in D7:D13

Please Login or Register  to view this content.

I don't know if I explained myself better.


Appreciate your help.

Here you go.

Note: The formula you supplied was slightly incorrect. I fixed it and put it into code so it should automatically correct itself after first update.

Please Login or Register  to view this content.

Once againg stnkynts, you made it happen, you've got some wizard skills!

That is just perfect, it updated the formula after the first run and it makes everything so clean and neat for the end user.

Thank you so much!

@stnkynts Hey I,ve worked a little bit on the tool you helped me with but I am wondering if you know how to make it automatically update, especially update the macro and not having to do it manually when I add an Item(row) in the "Inputs" DS and respectively (a column) in the "Database" column. It would be even better if I could add a row in "Inputs" and it would automatically add it in the "Database" and fix the macro.

Here's the database.

BTW I added some formatting but it is not the most efficient I guess.