How can I create multiple values from each cell of a column?

[MojoZ]

Hello. I am trying to expand a list of elements in calc, in a list differentiated elements. I want to clone each cell of a column, to three different cells with three different suffixes.
I want this to be produce automatically (by dragging several cells down)

A	B
 a	 a_suf1

b	a_suf2

c	a_suf3

d	b_suf1

e	b_suf2

f	b_suf3

g	c_suf1

h	c_suf2

I	c_suf3

j	d_suf1

k	d_suf2

l	d_suf3

etc

However, the formula B1 = A1&"_suf1", B2 = A1&"_suf2", B3 = A1&"_suf3", B4 = A2&"_suf1"…B6=A2&"_suf3" if dragged skips 2 cells of column A for each iteration.

A	B
 a	 a_suf1

b	a_suf2

c	a_suf3

d	d_suf1

e	d_suf2

f	d_suf3

g	g_suf1

h	g_suf2

I	g_suf3

j	j_suf1

k	j_suf2

l	j_suf3

Can anyone propose a solution to this?

[mariosv]

Copy B2:B3   

Paste in B4:Annn

[PYS]

Hi

One solution with a formula. Insert respectively in B1, B2, B3:

=INDIRECT("A"&ROUNDUP(ROW()/3;0))&"_suf1"
=INDIRECT("A"&ROUNDUP(ROW()/3;0))&"_suf2"
=INDIRECT("A"&ROUNDUP(ROW()/3;0))&"_suf3"

Then select B1:B3 and copy down… See 3Suffix.ods

Regards

[Lupp]

(It may be a genetic aberration: I do not like formulae changing from row to row and implementing a special case only - or generally solutions depending on specific interactive adaption once in a while. If I tried so - may be in slightly more complicated cases - I always forgot the procedures before needing them again. I succeeded, however, to memorise the procedure of filling down a single formula per column when needed.)

If you suffer from the same affliction, and you like parametrised solutions in similar cases, you may have a look into the attached example ask59769SimpleCartesianProduct001.ods
. It may not be extremely efficient, however. To improve things a bit you can introduce a helper column for the simple purpose of supplying the offset numbers needed more than once in the formulae.

(Getting the primary data and the suffixes as well from a database, a CROSS JOIN should be the means of choice.)