Compare cells in two excel sheet and then copy from one sheet to another.

hello all,

I need to update the information from one worksheet to another. i have two worksheets that i need to compare based on the contents of a row of cells. this contents of the row of cells is common to both worksheets. if the value from a cell is similar to that of the other. then i can replace the contents of the second sheet with that of the first. dont know if that is achievable...been brainstorming all week in this regard but dont have an answer yet...do anyone have a solution to this. this is how it is:

sheet 1
a b c
1 mike ,, 78416
2 wille ,, 78579
3 cary ,, 78580
4 grant ,, 78415
5 emma ,, 78435
6 duant ,, 78412
7 shere ,, 78431
8 larry ,, 78419


sheet 2
a b c d
1 unspecifieduser 25-Apr-13 7:11:11 PM 78410
2 unspecifieduser 26-Apr-13 4:22:26 PM 78410
3 unspecifieduser 26-Apr-13 4:26:22 PM 78416
4 unspecifieduser 25-Apr-13 10:38:34 AM 78435
5 unspecifieduser 25-Apr-13 1:48:43 PM 78416
6 unspecifieduser 25-Apr-13 2:44:52 PM 78416
7 unspecifieduser 24-Apr-13 12:03:43 PM 78431
8 unspecifieduser 25-Apr-13 7:55:35 AM 78416



the thing is this: if ((sheet 2, column d = sheet 1, column c) then replace (sheet 2, column a with sheet 1, column a))

this is to be achieved cell by cell....please anyone who can help here...thank you.

It is difficult to read the data in text format. Please attach an excel book with a desired result. To attach, go to advance then attachment.

Attached is the excel sheet.

if sheet 2!$D = sheet1!$C then replace sheet 2!$A with sheet 1 !$A.


thank you.

The columns are not exact mach. Am I right? E.g 78344/78345 would not able to find exact match in sheet2.

yeah but ignore that one...would have the column with just one number and not two. for the ones with one no. how do we match and replace the contents of sheet 2 column a with the corresponding contents from sheet 1 column A.

There are exact match for some cells, e.g. 78410 and up, but I do not know how are you going to match 78382? What is the nearest match? Is it 78381, 78383? So, exact match could easily be done.

this is not the whole sheet...there are some numbers omitted. this is just a section of the sheet and just represents a cross section. if it can be done for these then i will enlarge the formular to cover the rest. please ignore anything that doesnt match exactly. the essence is to get names of callers against the time spent on the telephone. it is a call detail record information but shows the users as unspecified instead of the name against the telephone user.

Try the attached. Exact match ONLY

yes thats exactly what i want to achieve. thank you.

but the sheet 2 shows is different every week and i need to be able to do this weekly and send as a report,

how did you achieve it and how can i go about it.

thank you once more.

Well, you run it when ever you want, but we can not use to clear the cells in Column A as you have data for some other cells.

hello, i don't understand your last statement. i want to be able to, using an excel formular achieve this, because the first sheet would always be the same but second sheet would change weekly so i want to be able to run this weekly before it is being sent...how do i do it. or do i have to keep coming to this forum to achieve it.

thank you.

I have now included a button on sheet1. Press it to run it. When you run the code, it will replace the values in column A.

thank you very much...i am presently working on the full excel sheet for sheet 1 that would be static. will send it to you as soon as i finish. the sheet 2 is the one that would change weekly. i would like you to put the run me button on this new excel sheet.


thank you.

The code will work with any sheet, as long as the sheet names remain the same, sheet1 an sheet2. If sheet1 is static that is even better as the button is in sheet1. Run the code on your actual data and see if the code works. If it does not work, tell me what is not working.

hello, sorry it took a while to do the full excel sheet but i had to manually get this data of all those involved so i can use it to compile the list. the format has changed a bit. there is serial number in sheet 1 and they both have the compared number in column D. i have placed the run me button but its not working. please check and help me once more with this.

thank you

Corrected.

Thank you very much...you have saved my day..thank you. i would like to know if any new numbers added to sheet one would reflect on sheet two if the number exists. i have checked the list and seen some "_unspecifieduser" which i want to get details and replace on sheet one so it can all be cleared when you click run me....dont let me bother you too much...thank you. would like to knw you better...my name is Tony Usoro and am on facebook...please feel free to add me as a friend...thank you.

You do not need a code to do this task. A simple match and index could have done the job. I have now include a formula sheet which gives you the same result as the code. I have also added a line on the code which clears existing data in column A when you run the code. You have now two solutions.
Please close this thread as solved.