• 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.

Vlookup Macro

sn152

Member
Hi All,

I am trying to do a vlookup macro. I have 2 workbooks named "Job" and "Job - Updated" which are stored in C:\Users\Desktop.

Now I want update the column "G" in the workbook "Job" using the data in the column "I" of workbook "Job - Updated". I would like to do this with a vlookup macro.

But one problem is that the range keeps on changing in both workbooks everyday. "Job" workbook will have all the data updated everyday. And it will be updated everyday using the "Job - Updated" workbook.

Please help me to create a macro for this.

Thanks in advance!
 

Attachments

  • Job.xlsx
    11.1 KB · Views: 6
  • Job - Updated.xlsx
    11.6 KB · Views: 5
Hi,

You can try to hard code the range with entire columns like "I:I". This may probably slow down the performance but still works well for your situation.

Note: You may need to keep the file names consistent with your code and location.

Regards,
Prasad DN
 
Hi Prasad,

Thank you for your reply. With vlookup we can update the existing records. But is there a way where we can automatically add new records if we found any to the file "job"?

Thanks!
 
Hi Somendra,

Please see the attached file. I have a code for vlookup. But I am not sure how to add the new records which are not there in the file "Job". It would be great if you could help me.

Thanks in advance!
 

Attachments

  • Job.xlsm
    16.2 KB · Views: 11
  • Job - Updated.xlsx
    11.8 KB · Views: 12
Hi,

I was away for some days, see your file now. I had inserted the code on module 2.



Regards,
 

Attachments

  • Job.xlsm
    20.5 KB · Views: 11
Hi Somendra,

Thank you. But this is adding the values in column H of workbook "Job - Updated" in column G of workbook "Job". But what I wanted was to add the records (whole row) that are not there in the workbook "Job" but available in "Job - Updated". Please help.
 
Hi Somendra,

Thank you. But this is adding the values in column H of workbook "Job - Updated" in column G of workbook "Job". But what I wanted was to add the records (whole row) that are not there in the workbook "Job" but available in "Job - Updated". Please help.

@sn152

Hi, I think there is some confusion regarding your post, In your first post you mention:

I want update the column "G" in the workbook "Job" using the data in the column "I" of workbook "Job - Updated". I would like to do this with a vlookup macro.
.

When I saw code on your file JOBS.xlsm on your comment #5, you were doing to bring status and end date to sheet.

So In my code I brought these field through VLOOKUP i.e. End Date & Status. Status is filled up in Column D and End Date in Column G.

Can you clarify your final requirement.

Regards,
 
Hi Somendra,

I apologize for the confusion caused. In the first post I was mentioning about the vlookup macro to update the status and End date. For that I added a code module 1. This code which I added will update the status and end date of only the records that are already present in "Job". Now if there are some additional records in "Job Updated", I want to add that also to this "Job" workbook i.e., I want to add those rows to the "Job" workbook.

For Example :
There are 50 records in "Job" workbook
And there are 60 records in "Job Updated" workbook

My code in module 1 will update the status and end date of those 50.

But the remaining 10 records in "Job updated" workbook also should be added to this "Job" workbook. And Iam looking for that code.

Thanks!
 
See the file Module3 has new code, this code will see the new JOB ID in Jobs file added in column A with the Jobs Updated file and fill the required field in JOBS file.

Regards,
 

Attachments

  • Job.xlsm
    23.6 KB · Views: 12
Hi Somendra,

This is not working for me... It is not pulling any new record from the "Job - Updated" workbook.
 
@sn152

One more thing came to my mind, when you say you are updating the new record, so you are updating them in Jobs-Updated file only and not on JOBS file. Is it?

Regards,
 
Hi Somendra,

No. I want the records to be updated in the workbook "Job" from the workook "Job - Updated". Please see the attached files. This is not working.
 

Attachments

  • Job - Updated.xlsx
    11.8 KB · Views: 7
  • Job (6).xlsm
    21.5 KB · Views: 2
Let me frame my question again:

1. You will update new records in Jobs-Updated manually ONLY?
2. When you press Button on JOBS file all the new records should come from from Jobs-Updated file to Jobs file without disturbing the old data of the file?

Regards,
 
Yes. What you said is correct. When I press a Button on JOBS file all the new records should come from from Jobs-Updated file to Jobs file.

In addition to this for some records in JOBS file, there may be updated status and end date in JOBS UPDATED file. So I need those also to be updated in JOBS file.
 
@sn152

Excuse me if I am taking a bit long on this, but if that is the case as you mentioned in your above comment, than why not each time you press the button whole data is updated.

Eg: Say On Day 1 you have data in Jobs-Updated file in the range A2:I10, so the required field of JOBS file will get populated directly which is equal to this range.

Now on Day 2 you add 10 more rows of fresh data and plus changed 2 old data in Jobs - Updated file. Now when you will run the macro the entire range in Jobs updated file will be pasted into jobs file.

If this is OK than there is no need of VLOOKUP, kindly confirm.

Regards,
 
Hi Somendra,

As you said, "Now on Day 2 you add 10 more rows of fresh data and plus changed 2 old data in Jobs - Updated file. Now when you will run the macro the entire range in Jobs updated file will be pasted into jobs file."

Every day the data keeps on adding to the JOBS file. And the JOBS UPDATED file will have less number of data compared to JOBS file. But there will be some records in JOBS UPDATED file that will have same job id as in the JOBS file.
So I need to add the data everyday from JOBS UPDATED to JOBS.
 
@sn152

Hi, See the file. Now no VLOOKUP is involved only Copy and paste operation. This will renew the data in JOBS files from JOBS-Updated file, each time macro will be run.

Macro Name: VlookupRev

Regards,
 

Attachments

  • Job (6).xlsm
    27.2 KB · Views: 4
Hi Somendra,

This code removes the existing data in JOBS file and pastes all the data from JOBS UPDATED file to JOBS file. But I want the old records to remain in JOBS file and new records from JOBS UPDATED file should be added to JOBS file.

Also few old records in JOBS file will have the updated status and date in JOBS updated file. I want that also to be updated in JOBS file. Please help.
 
@sn152

I am unable to understand one thing. Let me try to explain you what I understood.

On day 1:

You have data in JOBS-updated files say 10 rows of data, so you run the code and all the 10 rows of data will get added to JOBS file (only field which are there in JOBS files).

On day 2:
You added 5 new rows below the old 10 row of data in JOBS Updated file. Now you run the code so now all 15 rows will be added to jobs file.

On day 3:
You did not add any new data, but change the data on row 2 and row 5, and run the code so now all the data is again copied to JOBS file but now there will be 15 data as there is no new data but the old data that you updated will get updated here.

So what is the problem in here? Are you saying that if you change some data in old id you will not update the original row but instead will add a new row for the same id?



Regards,
 
What you said on "Day 2" has the problem. The data in JOBS updated file keeps changing everyday.

For example:

On Day 1: JOBS updated file and 10 records and when I run the code, that 10 gets updated in JOBS file.

Day 2: JOBS updated file has only 5 old records and 5 new records. Now when I run the code I want the 5 new records to be updated and for the 5 old records I want the status and date to be updated.

Hope I have explained clearly. Please let me know if you need any further details.

Thank you.
 
Ahaaa now it clear, your day 2 comment was the confusion point. So wait for some more time, will update you something.

Regards,
 
Back
Top