• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Check If Entry Exists: If Yes, Update. If No, Create?

M

MikeLanglois

New Member
Hey all,


I have quite a particular problem and I'm sure you guys can help. I did run a search, however its quite a specific problem so I wasn't able to get any hits.


The problem is as follows: We have raw data entered into a sheet called "Data". This information is updated weekly, and tracks information that has taken place in the last 31 days. So this means that some weeks, the key information will be the same, its just the additional information that will have updated. All information is relevant to a reference number, and that reference number doesnt change.


What I need to try and create (I'd imagine in VBA) is a code/macro that will check all new data against current data, and if the reference number is new, adds it to the next empty cell. If the reference number already exsists, it just updates the current information with the new information.


I wish I could upload an example, but the data I am using is sensetive, and I couldn't make enough sample data to accurately show what I mean. If an example is needed, I will try.


Does anyone have something like this or can help?


Thanks very much!

Mike
 
XL actually has a built in function that might be able to help you. It's called the Form tool. If you're using 2007+, you'll first need to find the Form button! Click Office button, Excel Options. Go to Customize, and drom first dropdown select "All criteria". Scrol down to "Form" and add it to the QAT. Now, select your data and then hit your new button. You'll get a Dialogue that lets you scroll through records, update them, search (via criteria) or add new data. Will that work?


If not, you'll need to provide some specific layout details. What column is information in, what row, is case-sensitivity important, etc?
 
Hey Luke,


Thanks for your help. Looking at the tools, I don't think there is on there that meets my needs? All I have in that option is "Summarize with pivot table" and "Remove duplicates"?


The sheet that will have all the data is called "Data", and Row 1 are the headers. Rows 2 and onwards contain data, and can be anywhere from 70-150 records. Column A will be the key indicator reference number. Because of the nature of this data, there are times when the reference number will be the same, however it will have updated information. What we would need is is something that will see if the reference number already exists, and if it does, overwrite the information in that row. If the reference number doesn't exist, it creates an entry in the next free cell?


Is this possible? It looks like it may been to be two macros when described like that?
 
Hi Mike ,


Whenever a reference number is entered , if it is checked and found to exist , it is updated ; does this mean that there will not be any duplicates ?


You are talking about a sheet called Data , which contains all data ; where do you enter fresh data , in a separate sheet , or on this same sheet ? If it is the same sheet , is the data entry area separated from the data storage area ?


Since you cannot upload your data , if you can give more details about the columns involved , the specific range where fresh data will be entered , and so on , it will help in writing the correct macro at one go.


Narayan
 
Hi Nara,


Thanks very much. It does mean that there will never be duplicates. The reference number is unique. Its the information relating to that reference number that may change.


The sheet "Data" holds all current information. For the fresh data, that is copied into a sheet called "New Data", sorry I should have mentioned that before. (format would be the same, Row 1 headers, Row 2 onwards is data.) The macro will need to check reference numbers from New Data against Data, and if the reference numbers match, the row from New Data overwrites the row from Data. If there are no matches, it adds it to the next empty row.


Thanks very much :)
 
Hi Mike ,


OK. There are two sheets , Data and New Data :


Data is the one where data is stored ,


New Data the one where new data is entered.


Will the new data be added to whatever data is already entered on this sheet ?


What I mean is , suppose there are 100 rows of data on the New Data sheet ; new data will now be entered on the 101st row ; the macro should check whether the reference number in A101 matches any reference numbers in column A on the sheet named Data ; suppose such a match exists in A37 ; then the new data in row 101 on the New Data tab overwrites the data in row 37 on the Data tab.


Suppose there is no match ; suppose there are already 66 rows of data on the Data tab ; the new data from row 101 on the New Data tab will be entered in row 67 on the Data tab.


Is all of this correct ?


Narayan
 
Hey Nara,


That sounds like the exact outcome of what I am looking for. That would keep any data that is already inputted/already on record updated, while adding new records should it be new information.


Is that something that is possible?
 
Hi Mike ,


Certainly it is possible ; but I can post a solution only tomorrow morning. I hope you can wait.


If not , there are a lot of other forum members who can help out.


Narayan
 
Hi Nara,


That address is currently blocked at my work. However I can test later on this evening at home.


Thanks very much for your help!
 
Hi Nara,


Was able to download and check that file and it works perfectly. So far it is doing everything that is needed!


Thanks very much for your help! :D


Mike
 
You must log in or register to reply here.
Back
Top