Search string in two columns in different excel sheets and copy the matching data

[kiranbvsn]

Hi Team,
Need vba sub or function for the below requirement.
I got three excel sheets, with 5 columns in each sheet.

I need to search the first column (which is company name) on sheet1 against other two sheets column1, here names are not consistent

for example
sheet1: column1: has "ABC Ltd" and
sheet2:column1 has "ABC" but
sheet3;column1 (which is standard for names is ABC US Limited).

This way I need to copy the data in other columns from sheet2 (if data matches) to sheet 1.

Can you please help.

[offthelip]

This is difficult to help without getting some idea about what the difference is between the columns that you are trying to compare.
The way I would approach this is to get rid of all the character strings in the name which can change between one version of the name and another by using code something like this:


Sub tst()
Dim arr(1 To 4) As String
arr(1) = "ltd"
arr(2) = " Co."
arr(3) = "Ltd"
arr(4) = "Company"


inarr = Range("A1:A10")
For i = 1 To 10
For j = 1 To 4
inarr(i, 1) = Replace(inarr(i, 1), arr(j), "")
Next j
Next i


Range("B1:B10") = inarr




End Sub



this works on tst data that looks like this:



ABC
ABC


ABC
ABC


ABC ltd
ABC


ABC Co. ltd
ABC


ABC Company Ltd
ABC