Replace Cell that matches a cell in another worksheet with the next cell

[Shades1970]

I would like to replace the contents in Cell F in sheet1 if it matches Cell B in sheet2 and replace Cell F on sheet1 with the data in Cell A on sheet2. This needs to be done for the entire column Sheet1 has some 400 rows with duplicates and sheet2 has 120 with the master information. I have been going round and round in circles trying many different codes that I have found but nothing works feeling frustrated any help would be grateful many thanks

PS
Cell F in sheet1 = vehicle reg
Cell G in sheet1 = Vehicle reg - make model

Cell B in sheet2 = vehicle reg
Cell A in sheet2 = fleet no

I dont know if it would be better to use a partial match using Cell G sheet1 with Cell B sheet2 and replace Cell F sheet1 with data from Cell A sheet2.

[TMS]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Shades1970]

Here is a sample of the workbook there are 3 worksheets Sheet1, Sheet2 & Sheet1 after VBA to to what I am trying to do many thanks for looking at this post

[TMS]

Try this (on a copy of your data):

Please Login or Register  to view this content.

[Shades1970]

TMS many thanks for that I tried it in my sample workbook all works fine so I then tried it in my actual workbook an it works 99.9% as it tends to miss out 4 vehicle reg and does not change them. I have checked to make sure that they are the same which they are i.e. no spaces etc. It is not to much of a hard ship I can work with it but unsure why it misses the same 4 vehicle reg No each time I run it any ideas.

Many thanks once again for what you have done.

[TMS]

Possibly leading or trailing spaces on either the source or the target. It is designed such that, if it doesn’t find a match, it leaves the source as is.

Post a sample workbook with just the problem registrations (source and target).

[Shades1970]

I copied and pasted the vehicle Reg that it missed into my test workbook and it all worked as I said I thought that there may have been a trailing spaces in the sheet2 but there wasn't. I have just checked in sheet1 and there is a trailing spaces in here. The data in sheet1 is downloaded from a work base software as a report so we do not populate it. is there any way round this?? thanks again

Is there a way to check for trailing spaces and remove them first?

[TMS]

Use Trim.

Please Login or Register  to view this content.

[Shades1970]

Many thanks TMS that works a treat all sorted you are the Forum Guru

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[Shades1970]

Already Done many thanks again for all you help