MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

to see in a cell value in a col. is contained in val any cell

  Asked By: Karina    Date: Dec 25    Category: MS Office    Views: 737

I have a worksheet (worksheet 1) with names of companies in a

There is another work sheet (worksheet 2) with names of companies
and an identification number in another column.

However The names of the same company in the two sheets are not
exact matches. for example

In sheet the company may be called ABC plc while in sheet 2 it may
simply called ABC.

I am looking for VBA code which will look at each cell in worksheet
1 and check if that is contained (or equal) in any of the cells in
the name column in worksheet 2 (i.e has to loop through name column
in worksheet 2 to check) if a match is found it has to pick the
company name and identification number in another worksheet. or if
possible in worksheet i itself.

then loop down to the next cell in worksheet 1 column1 and do the
same procedure..



4 Answers Found

Answer #1    Answered By: Adelfrid Fischer     Answered On: Dec 25

are you saying you want to do a "fuzzy" search?

Answer #2    Answered By: Barabas Cohen     Answered On: Dec 25

yes i guess it would be a fuzzy search. In the bulit in advanced filter option
in excel there is an option to filter " contains". I wish to use that together
with looping.

i.e what the script should do is..

pick the first cell  in the column1 of a worksheet  (a1), then see if it (
the value of a1) is contained  in any cells  say of the column10 of worksheet2 or
to keep it simple of the same worksheet.

and if it finds a cell which 'contains' or 'exactly matches' (a1) then it
does some other action like pasting the adjacent cell into say b1 of workshhet2
or same worksheet or colors the adjacent cells red

Answer #3    Answered By: Stefan Thompson     Answered On: Dec 25

Here is a function that may help. It is similar to Match(), but uses
InStr() to return the position of lookup_value in lookup_array if
lookup_value is contained  anywhere in the cells  of lookup_array.

Then, you can use INDEX() to get the identification number.

Function Match2(lookup_value, lookup_array)
n = 1
For Each c In lookup_array
If InStr(c, lookup_value) > 0 Then
Match2 = n
Exit Function
End If
n = n + 1
Match2 = False
End Function

Answer #4    Answered By: Ivan Coleman     Answered On: Dec 25

Can u give us more examples of different names?
Amount of characters?
Amount of words?
First word always the same?
First word always spelled correctly?