recognizing floating names with excel

aminexcel

Board Regular
Joined
May 2, 2009
Messages
63
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? ;)
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,790
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Feb 24, 2003
Messages
1,790
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
BCDE
1hosseinobamaaabehimnooss1
2hamiltonmarkaahiklmmnort2
3markhamiltonaahiklmmnort2
4aminexcelaceeilmnx3
5excelaminaceeilmnx3
6josephstiglitzeghiijlopssttz4
7stiglitzjosepheghiijlopssttz4
Sheet1
 

ColinKJ

Well-known Member
Joined
Jan 27, 2009
Messages
983

ADVERTISEMENT

Hi aminexcel,

The following code does it:

Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,424
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 2, 2009
Messages
63
"jimrward", "ColinKJ" and "Peter_SSs"

thanks a lot for your solutions
it solved my problem

sincerely
aminexcel
 

Forum statistics

Threads
1,136,333
Messages
5,675,175
Members
419,552
Latest member
jsanjur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top