Ask Your Question
0

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

asked 2018-05-13 08:40:04 +0200

Essay gravatar image

updated 2018-05-13 08:48:56 +0200

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

Comments

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-05-13 08:52:59 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2018-05-13 08:55:36 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2018-05-13 09:19:13 +0200

Lupp gravatar image

updated 2018-05-13 11:42:14 +0200

=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.

edit flag offensive delete link more

Comments

Please move your formula to your answer, since you have more robust solution.

Mike Kaganski gravatar imageMike Kaganski ( 2018-05-13 09:24:07 +0200 )edit
0

answered 2018-05-13 09:05:39 +0200

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.

edit flag offensive delete link more

Comments

Original comment removed acting on a comment by @Mike Kaganski .

Lupp gravatar imageLupp ( 2018-05-13 09:15:20 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-05-13 08:40:04 +0200

Seen: 50 times

Last updated: May 13 '18