Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-08
4
Medium Priority
?
3,226 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 248 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:
Arno Koster earned 252 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
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

886 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