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]

 

[SteveO59L]

Doesnt seem that difficult.

Do different card types have different prefix numbers ?

How do you get 6*2=1 ?

 

[marklane]

...

Yes different card types have different prefixes and those prefixes are different lengths and so are the card numbers.

6 * 2 = 12 but in the LUHN check it will be 1+2. Each single number added

 

[SteveO59L]

First thing I would do, would be to tabulate the card type and prefix numbers. Then you can use vlookup() to check the validity of the number/card combination.

 

[marklane]

Sorry but how do you mean?

 

[Exceljeanie]

Hi,
would these be correct numbers for mastercard?

52594879886892
52592464386666
52932218935071
52723586204202

Regards

 

[pgc01]

Hi marklane

Do you prefer a formula or a vba solution?

Kind regards
PGC

 

[marklane]

looking good

The first ones look fine thanks. I only need 1 card number for each card type (there are over 500 of them). I am using an automated test tool that will enter the number from the excel sheet and so need to generate one number for each card type

 

[marklane]

A formula would be best but a VBA solution is fine as long as it can be called into the relevant cell to generate the number

Thanks

 

[Richard Schollar]

Hi Mark

I believe the following will auto-generate card numbers (as long as the length of the number is an even number) and will perform the LUHN check:

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	6334505258150527	16	52	FALSE
3	visa	credit	16	4535	4535497675846244	16	90	TRUE
4	Mastercard	credit	14	52	52252750810402	14	38	FALSE
Sheet1

Formula in E2 copied down:

=D2&LEFT(TEXT(RAND()*1000000000000,"000000000000"),C2-LEN(D2))

Formula in F2 (just a check):

=LEN(E2)

Formula in G2 (this is an array formula so needs to be confirmed with Ctrl+Shift+Enter):

=SUM(--MID(TEXT(MID(E2,ROW(INDIRECT("1:"&F2)),1)*(MOD(ROW(INDIRECT("1:"&C2)),2)+1),"00"),{1,2},1))

Formula in H2:

=MOD(G2,10)=0

You can repeatedly hit F9 to recalculate alternative Credit card numbers that will be checked.