A really tough one.....impossible??

M

marklane

New Member
Joined
Apr 19, 2007
Messages
10
Hi

I am curently testing a web application and part of that requires me to create some test credit card data to perform checks on each type of card there is. What I have is a spreadsheet with the following:

CARD_NAME - CARD_TYPE - CARD_LENGTH - PREFIX
solo debit 16 633450
visa credit 16 4535
Mastercard credit 14 52

The first check the system does is to check that the card entered begins with the correct PREFIX e.g. 633450. The secon is checks is that the CARD_LENGTH is correct length. So first of all I need a formula that generates a card number in another celll say E1 dependant on these these things e.g.
E1 for the first line would generate a number that is 16 numbers in length and starts 633450 (the 16 numbers include the 633450 so would need another 10).

To make this even harder there is another check the sytem does called a LUHN check which does the following:
To check the LUHN digit of a card number for example 6333 0410 0135 4512 , each digit is multiplied by the alternating factors 2 and 1 (last digit is always multiplied by 1). Of each calculation result, the digits of the results are summed together. Then these sums are totalised.
e.g.
6 3 3 3 0 4 1 0 0 1 3 5 4 5 1 2
x x x x x x x x x x x x x x x x
2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1
1+2 +3+6+3 4+2+ 1+6+5+ 8+5+2+2 =50

The result is divisible by 10 withut a remainder so the card passes the check.

So to recap:

The card must start with the relevant prefix included in the number. The lenght is generated dependant on the card length field and it MUST pass the LUHN check!!
Don't want much do I ;-) :-D

I know this is probably not possible but I know how good you guys are so what do you think???? P[/b]
 
Sort by date Sort by votes
Hi again

This is a formula solution. I use 1 auxilliary column.

- In column G I build the n-1 first digits with a simple random number generator.

- In column E I add the Check Digit according to Luhn (equivalent to Mod 10) algorithm.

After you generate the numbers Copy, Paste Values column E and delete column G


In G2:
=D2&TEXT(INT(RAND()*10^(C2-LEN(D2)-1)),REPT("0",C2-LEN(D2)-1))

In E2:
=G2&MOD(1000-SUM(IF(MOD(ROW(INDIRECT("1:"&LEN(G2&0))),2)=1,--MID(G2&0,1+LEN(G2&0)-ROW(INDIRECT("1:"&LEN(G2&0))),1),MOD(2*MID(G2&0,1+LEN(G2&0)-ROW(INDIRECT("1:"&LEN(G2&0))),1),10)+INT(2*MID(G2&0,1+LEN(G2&0)-ROW(INDIRECT("1:"&LEN(G2&0))),1)/10))),10)

This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER.

Copy down

Remark: I had already posted this formula along with a UDF for Alphanumeric Mod 10 here:

http://www.mrexcel.com/board2/viewtopic.php?p=1215459#1215459

Hope this helps
PGC
Book1
ABCDEFGH
1CARD_NAMECARD_TYPECARD_LENGTHPREFIXNumberNumberw/oCD
2solodebit166334506334508587787322633450858778732
3visacredit1645354535165462133510453516546213351
4Mastercardcredit1452522772583377585227725833775
5
Sheet2
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Thanks a lot

This is great thanks. Does this work for odd and even lengths for card lengths e.g. normally they are 16 but there are a few that are 19
 
Upvote 0
Hi again

Mine does work with odd length card numbers - an alternative solution I attempted (but didnt post) was the one that failed with odds. I did need to extend the credit card number generator though:
Book1
ABCDEFGH
1CARD_NAMECARD_TYPECARD_LENGTHPREFIXCalculated Card NumberLen Cal CnumLUHN NoLUHN Check
2solodebit1663345063345050981342571658FALSE
3visacredit16453545352751684533711676FALSE
4Mastercardcredit1452520250066357371439FALSE
5Mastercardcredit19526952699518114664972321990TRUE
Sheet1

New formula in E2 copied down is:

Code: 
=D2&LEFT(TEXT(RAND()*10000000000,"0000000000")&TEXT(RAND()*10000000000,"0000000000"),C2-LEN(D2))
 
Upvote 0
Hi,
just written not optimized:

<nobr><span style="font-family:Courier New,Arial; font-size:9pt ;" ><span style="color:#000080"; >Public</span> <span style="color:#000080"; >Function</span> GetCardNumer(TheCount <span style="color:#000080"; >As</span> Long, ThePrefix <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span>) <span style="color:#000080"; >As</span> <span style="color:#000080"; >String</span>
<span style="color:#000080"; >Dim</span> i, sTemp <span style="color:#000080"; >As</span> String, bHelp <span style="color:#000080"; >As</span> Boolean, iMultTemp <span style="color:#000080"; >As</span> <span style="color:#000080"; >Byte</span>
<span style="color:#000080"; >Dim</span> iRnd <span style="color:#000080"; >As</span> Byte, zSum <span style="color:#000080"; >As</span> <span style="color:#000080"; >Long</span>
Application.Volatile <span style="color:#008000"; >' perhaps not depends</span>
Randomize Timer
<span style="color:#000080"; >Do</span>
   bHelp = False: zSum = 0: sTemp = ""
   <span style="color:#000080"; >For</span> i = TheCount - Len(ThePrefix) <span style="color:#000080"; >To</span> 1 <span style="color:#000080"; >Step</span> -1
      iRnd = Int(Rnd * 10)
      sTemp = iRnd & sTemp
   <span style="color:#000080"; >Next</span> i
   sTemp = ThePrefix & sTemp
   <span style="color:#000080"; >For</span> i = Len(sTemp) <span style="color:#000080"; >To</span> 1 <span style="color:#000080"; >Step</span> -1
      bHelp = <span style="color:#000080"; >Not</span> bHelp
      iMultTemp = <span style="color:#000080"; >Cbyte</span>(<span style="color:#000080"; >Mid</span>(sTemp, i, 1)) * (bHelp + 2)
      <span style="color:#000080"; >If</span> iMultTemp > 9 <span style="color:#000080"; >Then</span>
         iMultTemp = <span style="color:#000080"; >Cbyte</span>(Left(CStr(iMultTemp), 1)) + <span style="color:#000080"; >Cbyte</span>(Right$(CStr(iMultTemp), 1))
         
      <span style="color:#000080"; >End</span> <span style="color:#000080"; >If</span>
      zSum = zSum + iMultTemp
      
   <span style="color:#000080"; >Next</span> i
   <span style="color:#000080"; >If</span> zSum Mod 10 = 0 <span style="color:#000080"; >Then</span> <span style="color:#000080"; >Exit</span> <span style="color:#000080"; >Do</span>
<span style="color:#000080"; >Loop</span>
GetCardNumer = sTemp
<span style="color:#000080"; >End</span> <span style="color:#000080"; >Function</span>

</span></nobr>


<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:53px;" /><col style="width:64px;" /><col style="width:157px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >D</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >count</td><td >prefix</td><td >Number</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">14</td><td style="text-align:right; ">52</td><td style="text-align:right; ">52965758315130</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">16</td><td style="font-family:Verdana; font-size:9pt; text-align:right; ">633450</td><td style="text-align:right; ">6334509104447598</td></tr></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td >Spreadsheet Formulas</td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D3</td><td >=GetCardNumer(B3,C3)</td></tr><tr><td >D4</td><td >=GetCardNumer(B4,C4)</td></tr></table></td></tr></table>



regards
 
Upvote 0
Hi again

Here's another UDF:
Code: 
Function GenerateCardNumer(iCount As Integer, sPrefix As String) As String
Dim i As Integer, sCNumber As String, iFactor As Integer, iMod10ChkDig As Integer

' Generates number with n-1 digits
sCNumber = sPrefix
Randomize
For i = 1 To iCount - Len(sCNumber) - 1
    sCNumber = sCNumber & Int(Rnd * 10)
Next

'Calculate the check digit according to Luhn's algorithm
For i = Len(sCNumber) To 1 Step -1
    iFactor = 2 - (Len(sCNumber) - i) Mod 2
    iMod10ChkDig = iMod10ChkDig - (iFactor * Mid(sCNumber, i, 1) Mod 10 + iFactor * Mid(sCNumber, i, 1) \ 10) + 10
Next

GenerateCardNumer = sCNumber & iMod10ChkDig Mod 10
End Function

Best regards
PGC
 
Upvote 0
Hi Richard

I found this one interesting because the code solution is exactly the same as the formula solution. The first group of statements is the equivalent to the formula in the auxilliary column and the second group of statements is equivalent to the formula in column E.

Or maybe I call it interesting but it's just lack of imagination ... :-D

Cheers
PGC
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Simulator for one event in blackjack game
Replies
2
Views
540
baha17
B
S
  • Solved
need between data only
2
Replies
19
Views
396
MARK858
MARK858
T
  • Solved
Two Value if true, one value if false
Replies
5
Views
428
Joe4
Joe4
G
  • Solved
Run-Time Error 381
2
Replies
12
Views
201
Micron
M
E
  • Question Question
Generate unique code based on user form entry and put in table
2
Replies
18
Views
503
Micron
M

Forum statistics

Threads
1,224,811
Messages
6,181,081
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top