Need macro to search column A Sheet 1 for a similar match (about 6 common characters in the same order in the cell) in Sheet 2 then cut column B,C,D,E

[LuckyLuciana]

I have a bit of a complex problem that i have been having trouble fixing.

We currently perform alot of data entry and i would like this macro to avoid doing it manually. We recieve a list from our client and in column A is a list of their clients. I need the macro to verify as close a match as possible (they write the client names alittle differently from us so i need a closee match but it will likely not be a perfect match) from Sheet 1 to our spreadsheet, sheet 2.

Once that match is found, i want the macro to CUT the close match from Sheet 1 the row and data in columns B, C, D,E, F & G then PASTE that data to its similar match in Sheet 2 into columns F,G,H,I, J & K. Crazy bonus points to format the pasted data in Orange so i can see in sheet 2 which data has been updated and then MANUALLY delete the un matching rows (these are the deleted clients). I want this part done manually so we can ensure no mistakes have been made. I will likely change this later.

The biggest issue i have is i dont know how to code for a close match. it's not an exact duplicate. Here is an example:
Sheet 1 Column A client name: APRC7 APROPOS AFTER NINE
Sheet 2 Column A client name: Apropo After nin

I could save the column as txt then csv and then delete the codes but there are some that are reversed anyway and to do that all manually when i dont need to seems silly since every month there are new and deleted clients.

Would appreciate any insight and help, thanks so much for taking the time to read this!

 

[LuckyLuciana]

Macro to find similar match from sheet 1 in sheet 2 once found cut data into specific collumns

Hi everyone, not great with VB but i know this is possible...we are spending ALOT of time with data entry, this macro would help streamline this

i have loaded a screenshot in imgur for a visual as my last post not specific enough.

Top of screenshot is sheet 1, this file was a manipulated from a pdf version. I need the macro to look through column A to find closest match in sheet 2
Column A then cut the entire row (and col B C D E F &G ) from sh 1 and paste over the col A match sheet 2 but into specific columns there (see next image)

Bottom of screenshot is sheet 2, it is mandatory and cannot be changed unfortunately. I need the match from sh 1 to paste into col F G H I J &K respectively. If the macro can make these pasted lines a color so I know the data has been pasted that would be great because I need to see the uncoloured data as it represents something. Is this possible?

If its not possible to do a partial match then it would be best to cut form sheet 1 column A? i'm not sure anymore.

see image on imgur below,

Imgur: The most awesome images on the Internet

 

[Peter_SSs]

Re: Macro to find similar match from sheet 1 in sheet 2 once found cut data into specific collumns

i have loaded a screenshot in imgur for a visual as my last post not specific enough.

If you need to add further information like this, do not start a new thread. Instead, reply to your original thread with the additional information.

Refer to #12 of the Forum Rules and point 7 of the Forum Use Guidelines.

Hence I have merged your two threads.

 

[LuckyLuciana]

Re: Macro to find similar match from sheet 1 in sheet 2 once found cut data into specific collumns

Thanks Peter

 

[LuckyLuciana]

VB script needed to find Master list match, cut and paste columns when found

Hi Everyone! i have tweaked this abit and instead of needing a partial match, i have created a Master List with all the client names on it in column C and in col D assigned a unique ID number ("TRUE_COMPANY"). This way, if a client name repeats itself in the first spreadsheet the master list should set it straight. i have linked the master list in dropbox to help clarify. (small sample)
https://www.dropbox.com/s/bz79n955j88sfuy/Master List Sample drop box.xlsx?dl=0



First post spreadsheet: https://www.dropbox.com/s/zo8sn9mhiv4mj4r/READY FOR TESTING-CLEAN.xlsm?dl=0

First spreadsheet is the same as before, two tabs, one CX list, one TO list. Need to look down column A sheet 1 to find its unique number in Master list. once found, look down sheet 2 column A for unique number match.
IF Match= cut from sheet 1 row B-G and paste to sheet 2 F-K

Again, ANY insight on this would be huge. I dont know how to record the macro to find the match back from Master list to the man spreadsheet so i'm lost from the gate.
Lucky