Wednesday, December 7, 2011

yogi_Formulate Client ID Based On Entry Number And Modulo 13 Checkletter

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  
user MarkSpencerZ said:
It looks like I need an ArrayFormula, but I've never used it and can't quite figure out how to make it work for me. Could someone help ...
I want to generate a "Client ID" for each spreadsheet form entry (row).
Col W will contain the Client ID and will be calculated as
(1) adding the row number of each entry to a constant (142000)
(2) appending a modulo13 checkletter to the end of the result of (1). Modulo 13 is calculated by ...
    a) calculating the modulo13 value....  =([result from (1)]/13-INT([result from (1)]/13))*13+1
b) determining an alpha for the modulo 13 value.... =MID("ABCDEFGHIJKLM",[result from (2a)],1)
c) concatenating (1) & (2b)
Can anyone help me press this into an ArrayFormula(s)?

following is a solution to the problem