excel - Repeat a specific text x number of times in one column taken from another column of Random text strings -

i have text values in sheet 1, a4:a400 , each value in column there 10 codes in non-consecutive cells in row 1. want formula in "sheet 2" put each text value 10 times in column code in column b , repeat same new text value same 10 cods. like:

1 2   . . . . . . 5310    5200        3310    3456            3600 3   pk64                                 4   pk967                                5   pk106                                6   pk369                                7   pk250                                8   pk222                                

it should

1         b                            2   pk64    5310                             3   pk64    5200                             4   pk64    3310                             5   pk64    3456                             6   pk64    3600                             7   pk967   5310                             8   pk967   5200                             9   pk967   3310                             10  pk967   3456                             11  pk967   3600                             

i used =int((row()-1)/3)+1 increments , repeats number only.

i used =rept(b1,row()-1/5) repeats text in same cell increased number of times row number increments.

assuming codes numeric , no repetitions can this:

with pk's starting @ a4

write in j4 first pk (in example "pk64")

in k4 write formula:

=min(2:2) 

now write in j5 , k5 formulas:

=if(k5=min($2:$2);index(a:a;match(j4;a:a;0)+1);j4)  =if(k4=max($2:$2);min($2:$2);small($2:$2;rank(k4;$2:$2;1)+1)) 

copy, @ same time, both formulas down , pretended result

depending on regional settings may need replace ";" ","