I have few words in the range B2:B102. I have few more words in the
range I2:I10002. What I want to do is, compare the first word in the
range B2:B102 (B2.value) with first word in the range I2:I10002
(I2.value) and count how many characters (letters) are common and save
the count in the range C2.

Now compare B2 with I3 and list the count in C3. Go on comparing till
B2 with I10002 and list the count in C10002. Once this is finished,
move to second word in the range B2:B102 (B3.value).

Compare B3 with I2 and check the count. Now if this greater than the
value in cell C2, then update C2 with then new count else leave
C2.value as it is.

Let me explain the problem instead of the logic. By the way, there is
some change in the data. In the column B I have few names (it can be in
the format "first name and last name initial" or "first name initial
and last name" or "first name and last name full" or "only first name"
or "only last name"

...
I have few more names in the range I2:I582. These are unique names with
no duplicates.

...
In order to remove duplicates from the column B I want to compare every
name in column B with every name in the range I2:I582. I thought
counting characters would be the best way to eliminate the duplicates.

Please let me know your views.....


your method is right if in the I2:I582 you have all the names that you need
:-)

If not, it's quite difficult... one way could be to separate all the names
from the surnames and then choose (from the same surnames) only the surname
with the longest name.
This is not assuring a good selection anyway, it should be checked if there
are different names with the same surname for example

Bye

FM

The seperation is almost done.

Thank you.


FM wrote:
Let me explain the problem instead of the logic. By the way, there is
some change in the data. In the column B I have few names (it can be in
the format "first name and last name initial" or "first name initial
and last name" or "first name and last name full" or "only first name"
or "only last name"

..
I have few more names in the range I2:I582. These are unique names with
no duplicates.

..
In order to remove duplicates from the column B I want to compare every
name in column B with every name in the range I2:I582. I thought
counting characters would be the best way to eliminate the duplicates.

Please let me know your views.....


your method is right if in the I2:I582 you have all the names that you need
:-)

If not, it's quite difficult... one way could be to separate all the names
from the surnames and then choose (from the same surnames) only the surname
with the longest name.
This is not assuring a good selection anyway, it should be checked if there
are different names with the same surname for example

Bye

FM