Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real estate) and I'd love to be able to create a smaller keyfield that's readily reproducible in case I need to do a VLOOKUP from another workseet or workbook.

I'm thinking a hash from the title that'd be unique and reproducible for each title would be most appropriate. Is there a function available, or am I looking at developing my own algorithm?

Any thoughts or ideas on this or another strategy?

share|improve this question

4 Answers 4

up vote 10 down vote accepted

You don't need to write your own function - others already did that for you.
For example I collected and compared five VBA hash functions on this stackoverflow answer

Personally I use this VBA function

  • its called with =BASE64SHA1(A1) in Excel after you copied the macro to a VBA module
  • requires .NET since it uses the library "Microsoft MSXML" (with late binding)

Public Function BASE64SHA1(ByVal sTextToHash As String)

    Dim asc As Object
    Dim enc As Object
    Dim TextToHash() As Byte
    Dim SharedSecretKey() As Byte
    Dim bytes() As Byte
    Const cutoff As Integer = 5

    Set asc = CreateObject("System.Text.UTF8Encoding")
    Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")

    TextToHash = asc.GetBytes_4(sTextToHash)
    SharedSecretKey = asc.GetBytes_4(sTextToHash)
    enc.Key = SharedSecretKey

    bytes = enc.ComputeHash_2((TextToHash))
    BASE64SHA1 = EncodeBase64(bytes)
    BASE64SHA1 = Left(BASE64SHA1, cutoff)

    Set asc = Nothing
    Set enc = Nothing

End Function

Private Function EncodeBase64(ByRef arrData() As Byte) As String

    Dim objXML As Object
    Dim objNode As Object

    Set objXML = CreateObject("MSXML2.DOMDocument")
    Set objNode = objXML.createElement("b64")

    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.text

    Set objNode = Nothing
    Set objXML = Nothing

End Function

Customizing the hash length

  • the hash initially is a 28 characters long unicode string (case sensitive + special chars)
  • You customize the hash length with this line: Const cutoff As Integer = 5
  • 4 digits hash = 36 collisions in 6895 lines = 0.5 % collision rate
  • 5 digits hash = 0 collisions in 6895 lines = 0 % collision rate

There are also hash functions (all three CRC16 functions) which doesn't require .NET and doesn't use external libraries. But the hash is longer and produces more collisions.

You could also just download this example workbook and play around with all 5 hash implementations. As you see there is a good comparison on the first sheet

share|improve this answer
    
Looks great. However, I don't have enough VBA experience to prevent Excel from returning #NAME?. View code > cut and paste code into new window -- within the correct worksheet in the navigator > save as macro-enabled worksheet > close and return to excel...anything else I'm missing? Do I need to compile it somehow? –  dwwilson66 Feb 13 '13 at 16:09
    
Yes...to clarify...i pasted it in the new code window that popped up when I went to worksheet tab > view code... Downloading the sample right now, but I would like to understand why excel doesnt recognize my code –  dwwilson66 Feb 13 '13 at 16:54
    
WooHoo...the sample sheet helped. Realized I'd pasted the code into and excel OBJECT window, not a MODULE window. I'm getting hashes in my workbook now! –  dwwilson66 Feb 13 '13 at 17:03

You can try this. Run a Pseudo# on two columns:

=+IF(AND(ISBLANK(D3),ISBLANK(E3)),"",CODE(TRIM(D3&E3))*LEN(TRIM(D3&E3))+CODE(MID(TRIM(D3&E3),$A$1*LEN(D3&E3),1))INT(LEN(TRIM(D3&E3))$B$1))

Where A1 and B1 store random seeds entered manually: 0

share|improve this answer

To my knowledge there is no hash function build into Excel - you'd need to build one as a User Defined Function in VBA.

However, please note that for your purpose I don't think using a hash is required or really advantageous! VLOOKUP will work just as well on 256 bytes as it'll be on a smaller hash. Sure, it might be a tiny bit slower - bit that is for sure so small that it is immeasurable. And then adding the hash values is more effort for you - and for Excel...

share|improve this answer
    
yeah...I know that, but just from a presentation standpoint, I'd rather display, say, 15 bytes of hash that 256 bytes of title in my frozen left pane... –  dwwilson66 Feb 13 '13 at 15:11

For a reasonably small list you can create a scrambler (poor man's hash function) using built-in Excel functions.

E.g.

 =CODE(A2)*LEN(A2) + CODE(MID(A2,$A$1,$B$1))*LEN(MID(A2,$A$1,$B$1))

Here A1 and B1 hold a random start letter and string length.

A little fiddling and checking and in most cases you can get a workable unique ID quite quickly.

How it Works: The formula uses the first letter of the string and a fixed letter taken from mid-string and uses LEN() as a 'fanning function' to reduce the chance of collisions.

CAVEAT: this is not a hash, but when you need to get something done quickly, and can inspect the results to see that there are no collisions, it works quite well.

Edit: If your strings should have variable lengths (e.g. full names) but are pulled from a database record with fixed width fields, you will want to do it like this:

 =CODE(TRIM(C8))*LEN(TRIM(C8))
       +CODE(MID(TRIM(C8),$A$1,1))*LEN(MID(TRIM(C8),$A$1,$B$1))

so that the lengths are a meaningful scrambler.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.