Excel Formula to paste A x B pattern

Question

I have two columns A and B like below

column A contains 1,2,3,4,5 and B contains a,b,c,d,e

I want in column C like 1a,1b,1c,1d,1e,2a,2b,2c,2d,2e,3a,3b and so on.

Answer 1

Try this formula.

=CONCATENATE((INDIRECT("A"&ROUNDUP(ROW()/5,0))),(INDIRECT("B"& IF((ROW()-(ROUNDDOWN(ROW()/5,0)*5))=0,5,(ROW()-(ROUNDDOWN(ROW()/5,0)*5))))))

Output