Solved

how do I find macro Excel formula to generate numbers per Luhn formula

Posted on 2011-09-08
4
2,541 Views
Last Modified: 2012-05-12
Hi, I'm looking for a macro in Excel to generate numbers per the luhn algorithm
0
Comment
Question by:Seidmich
4 Comments
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 62 total points
ID: 36507267
Not a macro.......buhis formula in B1 will give the required check digit given a number of any length in A1

=MOD(SUMPRODUCT(-MID(TEXT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A1)))+LEN(A1)+1,2)+1),"00"),{1,2},1)),10)

If your numbers have a fixed specific length then that all-purpose formula could probably be shortened to suit your exact requirements

regards, barry
0
 
LVL 19

Accepted Solution

by:
akoster earned 63 total points
ID: 36509214
For small numbers you could use
Sub generate_luhn_numbers()

Dim database()
Dim luhn_format As String

'-- initialise
luhn_length = 4
ReDim database(10 ^ (luhn_length), luhn_length)
luhn_format = ""
For pos = 1 To luhn_length
 luhn_format = luhn_format & "0"
Next pos

For candidate = 0 To 10 ^ (luhn_length) - 1
    Application.StatusBar = "Processing : " & Int(100 * candidate / (10 ^ luhn_length))

    '-- fill with all possible numbers
    rest = 0
    For digit = 0 To luhn_length - 1
        database(candidate, digit) = Val(Mid(Format(candidate, luhn_format), digit + 1, 1))
    Next digit
           
    '-- calculate
    luhn_value = 0
    For digit = 0 To luhn_length - 1
        If isOdd(digit) Then
            luhn_value = luhn_value + 2 * database(candidate, digit)
        Else
            luhn_value = luhn_value + database(candidate, digit)
        End If
    Next digit
    database(candidate, luhn_length) = Val(Right(10 - luhn_value Mod 10, 1))
    
    '-- export to excel
    Cells(candidate + 1, 1) = 0
    For digit = 0 To luhn_length
    Cells(candidate + 1, 1) = Cells(candidate + 1, 1) + database(candidate, digit) * 10 ^ (luhn_length - digit)
    Next digit
    
Next candidate

End Sub

Function isOdd(value) As Boolean
    isOdd = ((value Mod 2) = 1)
End Function

Open in new window

0
 
LVL 50

Expert Comment

by:teylyn
ID: 37087210
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel conversion issue with Sql server 14 45
Macro 6 48
Excel for Mac - How make those Tabs larger? 2 31
Update As Well As Add 6 34
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now