# In Calc, how do I pull info from three columns and put into the fourth column in rows grouped in threes? Sample data:

    A    B    C    D
1  .05  .05  .90   A1
2  .05  .10  .85   B1
3  .05  .15  .80   C1
4  .05  .20  .75   A2
5  .05  .25  .70   B2
6  .05  .30  .65   C2
7  .05  .35  .60   A3
8  .05  .40  .55   B3
9  .05  .45  .50   C3


I am trying to take the information in the first three columns and copy it to the fourth column so that in the fourth column I include A1 to C1, then A2 to C2, then A3 to C3, and so on. Is there any way to automate this?

edit retag close merge delete

In your data, it's unclear what you want to achieve (and it somewhat differs from textual description). Do you want to have text with cell names in the column D? like A1; B1; C1 in D1? Or do you need one of those names depending on some condition (say, which is the minimal)? Or do you need to have the three numbers from A1:C1 into D1 (like .05;.05;.90 in D1)? Or sum of those? Or min? What you show ("A1" in D1) doesn't match what you describe.

Or maybe I see what you mean. The column D is linearized table A1:C9, so it continues down below?

Sort by » oldest newest most voted

=IF(INDEX($A$3:$C$102;QUOTIENT(ROW(F3)-ROW(F$3);COLUMNS($A$3:$C$102))+1;MOD(ROW(G3)-ROW(G$3);COLUMNS($A$3:$C$102))+1)="";"";INDEX($A$3:$C$102;QUOTIENT(ROW(F3)-ROW(F$3);COLUMNS($A$3:$C$102))+1;MOD(ROW(G3)-ROW(G$3);COLUMNS($A$3:$C$102))+1))

Acting on the comment by @Mike Kaganski I posted the most robust single-cell-formula above. (The original one contained some minor flaws.) However the formula is really long now, and the advice to better use two helper columns should be taken serious.

Because of the mentioned flaws I also removed the original example and replaced it by this one.

There is also demonstrated a solution by UserCode on 'Sheet2'.
I am specifically interested in comments on the way I handle the passing of parameters to the final Sub there. Of course, the UC solution can only work if you permit "macro" action.

more You may use formula =INDEX($A$1:$C$9;(ROW()-1)/3+1;MOD(ROW()-1;3)+1) in D1, and drag-copy it down.

more