unsorted names and unique id's

  • Thread starter Thread starter Randall Senn
  • Start date Start date
R

Randall Senn

I have a list of a couple of thousand names as part of a
large spreadsheet. The list is dynamic (new names are
added every two weeks), contains duplicate names, and is
unsorted. It must remain unsorted as the sorting in the
spreadsheet is based on values in another column.

I wish to find a formula or formulas for the column or
columns to the right of the names list that will assign a
unique id number to each unique name, and do it
alphabetically. Names starting with A will get low
number, names starting with Z will get high numbers.
Duplicate names should get the same id number.

Help is appreciated.
 
Let A1:A8 house the following sample:

{"Name";"Bill";"John";"Antoine";"Damon";"Damon";"Jill";"Bill"}

where Name is a label.

In B1 enter:

=MATCH(REPT("z",255),A:A)

In B2 enter & copy down:

=IF(A2<>"",SUMPRODUCT((A2>OFFSET($A$2,0,0,$B$1,1))+0)+1,"")

Note that this formula copied to "a couple of thousands" cells will cause
noticeable slow down.
 
Forgot to add...

This is what you'll see as result in B1:B8...

{8;3;8;2;5;5;7;3}
 
There is probably a better way using macros, probably even a better formula.
..... but for what it is worth... below is a prototype that will combine
ASCII codes of each letter into a number. It is good for the first four
characters but can be modified for more. The formula would be very large if
the names got too long. I don't know if there is an upper limit on a
formula length. If there is you may run into trouble.

the name must be in A1 and the formula in B1
if "a bc" or "A BC" were in A1 then B1 would show 65326667

=LEFT(CONCATENATE(IF(MID(A1,1,1)<>"",CODE(UPPER(MID(A1,1,1))),""),IF(MID(A1,
2,1)<>"",CODE(UPPER(MID(A1,2,1))),""),IF(MID(A1,3,1)<>"",CODE(UPPER(MID(A1,3
,1))),""),IF(MID(A1,4,1)<>"",CODE(UPPER(MID(A1,4,1))),""),"00000000"),8)
 
If you want to try a macro......

Assuming the names are in column A and the "real sorting"
is based on column C, you might try the following macro.
It sorts on column A, inserts column B, assigns an ID in
column B based on the name in column A, and then re-sorts
the data based on column C.

Sub Assign_ID()

Application.ScreenUpdating = False

Dim NumRows As Double
Dim DupeCheck As String
Dim I As Double
Dim ID As Double

ID = 1
Range("A1").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("A2").Select
DupeCheck = ActiveCell
NumRows = Cells(1, 1).End(xlDown).Row
For I = 1 To NumRows - 1
If DupeCheck <> ActiveCell Then
ID = ID + 1
ActiveCell.Offset(0, 1).Value = ID
ActiveCell.Offset(1, 0).Select
DupeCheck = ActiveCell
Else
ActiveCell.Offset(0, 1).Value = ID
ActiveCell.Offset(1, 0).Select
End If
Next I

Range("A1").Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Application.ScreenUpdating = True
End Sub

If you are unfamiliar with macro code, lines 9-12 above
and the simmilar lines at the bottom of the routine are
actually one long line of code. Because of the font here
they look weird. If you paste and edit the code correctly,
you will end up with two lines in each location. One will
start with "Selection.Sort..." and end with the
uderscore "_". The second will start with "OrderCustom..."
and end with "..TopTo Bottom."
 
Back
Top