SERIES GENERATION IN GOOGLE DOCS
April 21, 2012 Leave a comment
Question in the Forum:
i need to use data in a column and add two different text strings to each item. this then must be put into a row.
so if my data is (a,b,c,d,) i want the output in my row to be: | a1 | a2 | b1 | b2 | c1 | c2 | d1 | d2 |.
i have used =TRANSPOSE(ARRAYFORMULA(A2:A5 & ” 1″)) to get | a1 | b1 | c1 | d1 | but i can’t figure out how to also add the other string.
the solution suggested by me is as follows:
now lets break down the formula
our first formula is
if we drag this formula across the columns we will be as shown below image
since $A1 is relative reference with column locked this will act as expanding range and will generate the series of numbers from 1,2,3,4,5,6…. etc.
but our objective is to generate the series as 1,1,2,2,3,3… etc. to convert the 1,2,3,4,5,6…etc into required series we have wrapped this in to CEILING function. now the formula is
now we got the required series, to convert the required series into a alphabet we are using the CHAR function
The formula will be =CHAR(64+CEILING(COLUMNS($A1:A1),2)/2)
Character 65 is A as per Ascii character set, we are using this to convert the number 1,1,2,2,3,3… etc into alphabits A,A,B,B,C,C……….etc
to get the number 1,2,1,2,1,2 we are using the if with MOD function,
With use of MOD function we are converting the odd column numbers to 1 and even column numbers to 2.