Unique Identifier

Why a ten digit identifier if only 95 rows? Why not just number them 1-95? Simpler is often the answer.

Hi,

It sounds like you will be creating a new 11th column for the unique ID. In which case as long as any sort includes all 11 columns then you will have no problems.

Originally Posted by mussonip

Hello Richard, I was thinking of doing a RANDBETWEEN but it recalculates on every sort.

...Sorry, but what's the question?

One way:

Row\Col

A

B

C

D

E

F

G

H

I

J

K

L

1

Sal

First

MI

Last

Add1

Add2

Cit

ST

Zip

Age

CRC

2

Mr

Bob

A

Smith

1234 Main St

#201

Dallas

TX

75240

22

4,203,160,230

K2: =GetCRC32(Cat(A2:J2, "|")) + 2^32

3

Mr

Bob

B

Smith

1234 Main St

#201

Dallas

TX

75240

22

2,231,331,328

4

Mr

Bob

B

Smith

1234 Main St

#201

Dallas

TX

75240

23

4,093,139,606

Function Cat(vInp As Variant, _
             Optional sSep As String = ",", _
             Optional bCatEmpty As Boolean = False) As String
    ' Catenates the elements of vInp separated by sSep
    ' Empty values and null strings are ignored unless bCatEmpty is True

    Dim vItem       As Variant
    Dim sItem       As String

    If bCatEmpty Then
        For Each vItem In vInp
            Cat = Cat & CStr(vItem) & sSep
        Next vItem

    Else
        For Each vItem In vInp
            sItem = CStr(vItem)
            If Len(sItem) Then Cat = Cat & sItem & sSep
        Next vItem
    End If

    If Len(Cat) Then Cat = Left(Cat, Len(Cat) - Len(sSep))
End Function

Function GetCRC32(sInp As String)
    ' UDF wrapper for iCRC32
    GetCRC32 = iCRC32(StrConv(sInp, vbFromUnicode))
End Function

Function iCRC32(aiBuf() As Byte) As Long
    ' shg 2013

    Const iPoly     As Long = &HEDB88320
    Static aiCRC()  As Long
    Static bInit    As Boolean

    Dim iCRC        As Long
    Dim i           As Long
    Dim j           As Long

    If Not bInit Then
        ReDim aiCRC(0 To 255)

        For i = 0 To 255
            iCRC = i

            For j = 8 To 1 Step -1
                If iCRC And 1 Then
                    iCRC = ((iCRC And &HFFFFFFFE) \ 2&) And &H7FFFFFFF
                    iCRC = iCRC Xor iPoly
                Else
                    iCRC = ((iCRC And &HFFFFFFFE) \ 2&) And &H7FFFFFFF
                End If
            Next j

            aiCRC(i) = iCRC
        Next i
        bInit = True
    End If

    Dim iLookup     As Integer

    iCRC32 = &HFFFFFFFF

    For i = LBound(aiBuf) To UBound(aiBuf)
        iLookup = (iCRC32 And &HFF) Xor aiBuf(i)
        ' shift right 8 bits:
        iCRC32 = ((iCRC32 And &HFFFFFF00) \ &H100) And &HFFFFFF
        iCRC32 = iCRC32 Xor aiCRC(iLookup)
    Next i

    iCRC32 = Not (iCRC32)
End Function

Nice one SHG. I'll have to dissect it to see how it works.

Thanks, CB.