I use the following formula in cell B1 to calculate a Vehicle Identification Number Check Digit:
=LOOKUP(MOD(SUMPRODUCT(LOOKUP(MID(A1,{1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17},1),{"0","1","2","3","4","5","6","7","8","9","A","B","C","D","E","F","G","H","J","K","L","M","N","P","R","S","T","U","V","W","X","Y","Z";0,1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,1,2,3,4,5,7,9,2,3,4,5,6,7,8,9}),{8,7,6,5,4,3,2,10,9,8,7,6,5,4,3,2}),11),{0,1,2,3,4,5,6,7,8,9,10;"0","1","2","3","4","5","6","7","8","9","X"})
In cell A1, I have the VIN.
In cell C1, I have use the formula:
=IF(MID(A1,9,1)=B1,"Y","N")
to determine if the check digit matches the VIN.
Is there a way, without using a macro, to reference the first formula for many VINs copied in column A without copying the formula to every cell in column B?
The formula is 392 characters long and the worksheet becomes very large when working with thousands of VINs.
=LOOKUP(MOD(SUMPRODUCT(LOOKUP(MID(A1,{1,2,3,4,5,6,7,8,10,11,12,13,14,15,16,17},1),{"0","1","2","3","4","5","6","7","8","9","A","B","C","D","E","F","G","H","J","K","L","M","N","P","R","S","T","U","V","W","X","Y","Z";0,1,2,3,4,5,6,7,8,9,1,2,3,4,5,6,7,8,1,2,3,4,5,7,9,2,3,4,5,6,7,8,9}),{8,7,6,5,4,3,2,10,9,8,7,6,5,4,3,2}),11),{0,1,2,3,4,5,6,7,8,9,10;"0","1","2","3","4","5","6","7","8","9","X"})
In cell A1, I have the VIN.
In cell C1, I have use the formula:
=IF(MID(A1,9,1)=B1,"Y","N")
to determine if the check digit matches the VIN.
Is there a way, without using a macro, to reference the first formula for many VINs copied in column A without copying the formula to every cell in column B?
The formula is 392 characters long and the worksheet becomes very large when working with thousands of VINs.