Character count

[maxirox]

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.

 

[FM]

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.

You don't specify exactly how to compare the two words, so the code here
could not be exactly what you want.
You can modify it to match your needs, it needs some time to run:


'*****************************************************************
Public Sub control()
Dim num As Integer 'number of matches
Dim a, b, c, d

For a = 2 To 102
For b = 2 To 10002
num = 0
For c = 1 To Len(Cells(a, 2))
For d = 1 To Len(Cells(b, 9))
If Mid(Cells(b, 9), d, 1) = Mid(Cells(a, 2), c, 1) Then num =
num + 1: Exit For
Next d
Next c
If a = 2 Then
Cells(b, 3) = num
Else
If num > Cells(b, 3) Then Cells(b, 3) = num
End If
Next b
Next a

End Sub
'******************************************************************


FM

 

[maxirox]

Thanx, this will work.

You don't specify exactly how to compare the two words, so the code here
could not be exactly what you want.
You can modify it to match your needs, it needs some time to run:

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"

The real data: I have 2270 names in the range "B2:B2271" I want to
narrow them down by eliminating the duplicates. There are lots of
duplicates in this range but I cannot use simple macros or formulaes to
remove the duplicates because of their above mentioned different
format.

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.....

 

[FM]

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

 

[maxirox]

The seperation is almost done.

Thank you.

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