Ask Your Question
0

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

asked 2015-10-21 11:35:20 +0100

MojoZ gravatar image

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 flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2015-10-22 00:31:56 +0100

m.a.riosv gravatar image

updated 2015-10-22 00:32:34 +0100

Copy B2:B3   

Paste in B4:Annn
edit flag offensive delete link more
0

answered 2015-10-22 08:01:00 +0100

pierre-yves samyn gravatar image

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

edit flag offensive delete link more
0

answered 2015-10-22 13:30:36 +0100

Lupp gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-10-21 11:35:20 +0100

Seen: 249 times

Last updated: Oct 22 '15