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

[marklane]

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

I know this is probably not possible but I know how good you guys are so what do you think???? P[/b]

 

[pgc01]

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
	A	B	C	D	E	F	G	H
1	CARD_NAME	CARD_TYPE	CARD_LENGTH	PREFIX	Number		Numberw/oCD
2	solo	debit	16	633450	6334508587787322		633450858778732
3	visa	credit	16	4535	4535165462133510		453516546213351
4	Mastercard	credit	14	52	52277258337758		5227725833775
5
Sheet2

 

[marklane]

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

 

[Richard Schollar]

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
	A	B	C	D	E	F	G	H
1	CARD_NAME	CARD_TYPE	CARD_LENGTH	PREFIX	Calculated Card Number	Len Cal Cnum	LUHN No	LUHN Check
2	solo	debit	16	633450	6334505098134257	16	58	FALSE
3	visa	credit	16	4535	4535275168453371	16	76	FALSE
4	Mastercard	credit	14	52	52025006635737	14	39	FALSE
5	Mastercard	credit	19	5269	5269951811466497232	19	90	TRUE
Sheet1

New formula in E2 copied down is:

=D2&LEFT(TEXT(RAND()*10000000000,"0000000000")&TEXT(RAND()*10000000000,"0000000000"),C2-LEN(D2))

 

[Exceljeanie]

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

 

[pgc01]

Hi again

Mine works too for odd numbers.

Kind regards
PGC

 

[Richard Schollar]

That's a neat function Exceljeanie

 

[Exceljeanie]

Hi Richard,

thanks

Regards Peter

 

[pgc01]

Hi again

Here's another UDF:

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

 

[Richard Schollar]

...and that one's just downright sneaky, PGC

 

[pgc01]

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 ...

Cheers
PGC