makako
02-17-2010, 08:24 PM
Hi, I know this can be done in VBA but I want to know if it is possible doing it in only one cell with no code. I need to take as an input a numeric ID in a client and generate a validation number. The validation number is generated like this:
Ex: ID = (In cell A1 for the formula below) 2023453
Validation generation:
SUM( MMULT( {2,0,2,3,4,5,3} "This array I would need to generate and dont know how, any ideas?" ; TRANSPOSE( "{" & LEFT(REPEAT("1,2,";INT(LEN(A1)/2)+1);(LEN(A1)*2-1) & "}" )) )
that creates the array
{=SUM(MMULT( {2,0,2,3,4,5,3} ; TRANSPOSE( {1,2,1,2,1,2,1} )))}
{=SUM(MMULT( {2,0,2,3,4,5,3} ; {1/2/1/2/1/2/1} ))}
{=SUM({ 2*1 , 0*2 , 2*1 , 3*2 , 4*1 , 5*2 , 3*1 })}
{=SUM({ 2 , 0 , 2 , 6 , 4 , 10 , 3 })}
=27
So, any ideas in using only excel turning 2023453 into {2,0,2,3,4,5,3}??
Ex: ID = (In cell A1 for the formula below) 2023453
Validation generation:
SUM( MMULT( {2,0,2,3,4,5,3} "This array I would need to generate and dont know how, any ideas?" ; TRANSPOSE( "{" & LEFT(REPEAT("1,2,";INT(LEN(A1)/2)+1);(LEN(A1)*2-1) & "}" )) )
that creates the array
{=SUM(MMULT( {2,0,2,3,4,5,3} ; TRANSPOSE( {1,2,1,2,1,2,1} )))}
{=SUM(MMULT( {2,0,2,3,4,5,3} ; {1/2/1/2/1/2/1} ))}
{=SUM({ 2*1 , 0*2 , 2*1 , 3*2 , 4*1 , 5*2 , 3*1 })}
{=SUM({ 2 , 0 , 2 , 6 , 4 , 10 , 3 })}
=27
So, any ideas in using only excel turning 2023453 into {2,0,2,3,4,5,3}??