recognizing floating names with excel

[aminexcel]

Hi All Members
i have this column in an sheet:

<table style="border-collapse: collapse; width: 126pt;" border="0" cellpadding="0" cellspacing="0" width="168"><col style="width: 54pt;" width="72"> <col style="width: 72pt;" width="96"> <tbody><tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt; width: 54pt;" align="center" height="19" width="72">
</td> <td class="xl65" style="width: 72pt;" align="center" width="96">A</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt;" align="center" height="19">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">aminexcel</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">stiglitzjoseph</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">3</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">markhamilton</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">4</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">hosseinobama</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">5</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">excelamin</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">6</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">hamiltonmark</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">7</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">josephstiglitz</td> </tr> </tbody></table>
how can i recognize floating names with a formula or macro. i want to have this result

<table style="border-collapse: collapse; width: 180pt;" border="0" cellpadding="0" cellspacing="0" width="240"><col style="width: 54pt;" width="72"> <col style="width: 72pt;" width="96"> <col style="width: 54pt;" width="72"> <tbody><tr style="height: 14.25pt;" height="19"> <td style="height: 14.25pt; width: 54pt;" align="center" height="19" width="72">
</td> <td class="xl65" style="width: 72pt;" align="center" width="96">A</td> <td class="xl65" style="border-left: medium none; width: 54pt;" align="center" width="72">B</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt;" align="center" height="19">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">aminexcel</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="center">1</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">excelamin</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="center">1</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">3</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">stiglitzjoseph</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="center">2</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">4</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">josephstiglitz</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="center">2</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">5</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">markhamilton</td> <td class="xl68" style="border-left: medium none;" align="center">3</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">6</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">hamiltonmark</td> <td class="xl67" style="border-left: medium none;" align="center">3</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl67" style="height: 14.25pt; border-top: medium none;" align="center" height="19">7</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="center">hosseinobama</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="center">4</td> </tr> </tbody></table>
any idea?

 

[jimrward]

there has been a recent post regarding sorting cell characters into alphabetic order, you could use this function to create another column which contains the sorted characters for each of your entries

this would give you a starting point for matching the names, we could then use this to maybe sort on the new column so that like minded people aggregate together and then would could apply a simple ranking/count to achieve your result

 

[jimrward]

Here is the link to the other post

http://www.mrexcel.com/forum/showthread.php?t=490638

 

[jimrward]

ok using the sortit function from above in Column C, i then sorted the data on column C, and B

and added a function to column D2 downwards, where D1 contains 1,

=IF(C2<>C1,D1+1,D1) and copied this down

whilst not in the same order as yours it functions the same

Book1
	B	C	D	E
1	hosseinobama	aabehimnooss	1
2	hamiltonmark	aahiklmmnort	2
3	markhamilton	aahiklmmnort	2
4	aminexcel	aceeilmnx	3
5	excelamin	aceeilmnx	3
6	josephstiglitz	eghiijlopssttz	4
7	stiglitzjoseph	eghiijlopssttz	4
Sheet1

 

[ColinKJ]

Hi aminexcel,

The following code does it:

Sub Match()
R = Sheets(1).Range("A65536").End(xlUp).Row
For a = 1 To R
    x = Mid$(Cells(a, 1), 1, 4)
    If Cells(a, 2) <> "" Then GoTo nexta
    Cells(a, 2) = a
    For b = 1 To R
        If a <> b Then
            xx = Cells(b, 1)
            For y = 1 To Len(xx)
                On Error Resume Next
                If Mid$(xx, y, 4) = x Then
                    If Cells(b, 2) = "" Then Cells(b, 2) = a: GoTo nextb
                End If
                On Error GoTo 0
            Next y
nextb:
        End If
    Next b
nexta:
Next a
Range("A1:B" & R).Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("C1").Select
End Sub

ColinKJ

 

[Peter_SSs]

Here's another approach to try.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Match_2()<br>    <SPAN style="color:#00007F">Dim</SPAN> myRng <SPAN style="color:#00007F">As</SPAN> Range, cl <SPAN style="color:#00007F">As</SPAN> Range, Found <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> S <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Swp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> N <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, L <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, R <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> myRng = Range("A1", Range("A" & Rows.Count).End(xlUp))<br>    <SPAN style="color:#00007F">With</SPAN> myRng<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cl <SPAN style="color:#00007F">In</SPAN> myRng<br>            <SPAN style="color:#00007F">If</SPAN> Len(cl.Offset(, 1).Value) = 0 <SPAN style="color:#00007F">Then</SPAN><br>                N = N + 1<br>                cl.Offset(, 1).Value = N<br>                S = cl.Value<br>                L = 1: R = Len(S) - L<br>                <SPAN style="color:#00007F">Do</SPAN><br>                    Swp = Right(S, R) & Left(S, L)<br>                    <SPAN style="color:#00007F">Set</SPAN> Found = .Find(What:=Swp, After:=cl, LookIn:=xlValues, _<br>                        LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)<br>                    L = L + 1: R = R - 1<br>                <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> And L > 0<br>                <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                    Found.Offset(, 1).Value = N<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> cl<br>        .Resize(, 2).Sort Key1:=.Cells(1, 2), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _<br>            MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

 

[aminexcel]

"jimrward", "ColinKJ" and "Peter_SSs"

thanks a lot for your solutions
it solved my problem

sincerely
aminexcel