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

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?

edit retag close merge delete

Sort by » oldest newest most voted
Copy B2:B3

Paste in B4:Annn

more

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

more

(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.)

more