Replace bad names from a list of good names

Hi Again,

Can someone help me create a script that will replace the names in column A on sheet1 from a Master sheet in the same workbook?

The problem is that different users are entering data on sheet1 col A in different ways example someone may enter Johnc or John C Or John What I want is for something to run down col A on sheet1 and look for the like name on the master sheet if the name matches then do nothing but if the name is like another name on the master sheet then replace the name if they are almost alike.

Anyway to do that?


Thanks as always!!!
Mike

I have a similar situation where I convert truncated resource names to full length names.

I use two sets of arrays where the truncated value and desired value share the same positions in their respective arrays, eg... array(n). You just use a loop to cycle through the list until you find a match.

But like Mickg said, you need a definitive list of values to compare with on both sides.

Originally Posted by MickG

Hi, It the list on the Master sheet just one name ??
Regards Mick

Yes just one name unless there is two John's example if there were two John's they would be John A and John B Other wise the list is just the first name.

Thank You, Mike

Hi, Try this:-
I assumed that the "Names" where in column "A" of both sheets.

Please Login or Register  to view this content.

Regards Mick

Originally Posted by MickG

Hi, Try this:-
I assumed that the "Names" where in column "A" of both sheets.

Please Login or Register  to view this content.

Regards Mick

Hi Mick, Thanks for your help, However I was unable to get this to work, I mean the script runs without any errors but wont change the names on sheet1 from the Master Sheet.

I was just hoping I didn't have to run through half the years data correcting peoples names. But that may be the easiest thing to do at this point.

JBeaucaire you are correct I have already done the change a couple weeks back with your suggestion on the validation. I should not have the problem going forward. It was just the first few months of data I needed to correct. I was hoping for a short cut...

Thanks Guys for trying. Mike

Hi, Can you attach you workbook (.xls) or a trial version.
Regards Mick

Originally Posted by MickG

Hi, Can you attach you workbook (.xls) or a trial version.
Regards Mick

Sure, Here is an example.

Thank You, Mike

Hi, This does'nt resolve the immediate problem, but it might be a solution.
On sheet "Master" there is a Button, and when clicked will colour all the cells in column "A" sheet (1) that Match the Names in sheet "Master". you could then alter the Incorrect Ones.
On sheet(1) there is a "SelectionChange Event" that will stop the incorrect entry of a name in column "A" that does not match one of the Sheet "Master" names. The code will show a Msgbox , Telling you the name is Incorrect and the Name will then be deleted.
This is about the best I can do. I think the original Problem could be sorted with a some alterations to the "Master" Sheet of names.
Regards Mick
Ps :- I've just seen Leith might have solved you problem !!

Thanks Guys. We are much further than before let me plug this into some real data. However it appears on the test book that it is working.

Thanks Again, Mike