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

New Member
Joined
Feb 6, 2014
Messages
10
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

New Member
Joined
Feb 6, 2014
Messages
10
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,169
Office Version
365
Platform
Windows
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

New Member
Joined
Feb 6, 2014
Messages
10
Re: Macro to find similar match from sheet 1 in sheet 2 once found cut data into specific collumns

Thanks Peter
 

LuckyLuciana

New Member
Joined
Feb 6, 2014
Messages
10
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
 

Forum statistics

Threads
1,081,543
Messages
5,359,431
Members
400,526
Latest member
Brook1083

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top