Hello Everyone,
| So here is the situation.. | ||||||||||
| As you can see, I am arranging data from array or table into a list.. | ||||||||||
| Actually this can be done with two different formulas based on similar kind of approaches… | ||||||||||
| Now first we will take INDEX Function Approach… The formula used is.. | ||||||||||
| =INDEX($A$1:$C$4,1+INT((ROWS($D$1:D1)-1)/3),1+MOD(ROWS($D$1:D1)-1,3)) | ||||||||||
| Table | Row No. | Column No. | ||||||||
| Actually, the trick lies in calculating Row and Column No. | ||||||||||
| Based on the TABLE INDEX we have used.. I.e. $A$1:C$$4, we have to use following Row and column No's | ||||||||||
| Row No. | Column No. | |||||||||
| 1 | 1 | |||||||||
| For Row no., I have used <---- | 1 | 2 | -----> For Column No., I have used | |||||||
| 1+INT((ROWS($D$1:D1)-1)/3) | 1 | 3 | 1+MOD(ROWS($D$1:D1)-1,3) | |||||||
| dragged down.. | 2 | 1 | dragged down… | |||||||
| 2 | 2 | |||||||||
| 2 | 3 | |||||||||
| 3 | 1 | |||||||||
| 3 | 2 | |||||||||
| 3 | 3 | |||||||||
| 4 | 1 | |||||||||
| 4 | 2 | |||||||||
| 4 | 3 | |||||||||
| Both of above formulas are purely mathematical… and easy one… | ||||||||||
| lets take the row no. first… which reduces to | ||||||||||
| =1+INT((row_increment-1)/No._of_Columns_in_Index) | ||||||||||
| if I drag it down .. It will result in like below… | ||||||||||
| =1+((1-1)/3) | =1+0=1 | INT((ROWS($F$1:F1)-1)/3) | ||||||||
| =1+((2-1)/3) | =1+0=1 | The bold part is the Row offset for OFFSET | ||||||||
| =1+((3-1)/3) | =1+0=1 | alternative.. | ||||||||
| =1+((4-1)/3) | =1+1=2 | |||||||||
| =1+((5-1)/3) | =1+1=2 | |||||||||
| =1+((6-1)/3) | =1+1=2 | |||||||||
| =1+((7-1)/3) | =1+2=3 | |||||||||
| =1+((8-1)/3) | =1+2=3 | |||||||||
| =1+((9-1)/3) | =1+2=3 | |||||||||
| =1+((10-1)/3) | =1+3=4 | |||||||||
| =1+((11-1)/3) | =1+3=4 | |||||||||
| =1+((12-1)/3) | =1+3=4 | |||||||||
| So one conclusion, one can easily draw.. That if we want to fix the row no. for certain rows dragged.. | ||||||||||
| we have to use INT construction.. Corrected by 1+ and -1 | ||||||||||
| there can be other alternatives too for this construction… so practice this using FORMULA AUDITING… | ||||||||||
| Now comes the column part.. The formula I used is.. | ||||||||||
| 1+MOD(ROWS($D$1:D1)-1,3) which means.. | ||||||||||
| =1+Mod(Row_increment-1,No._of_Columns_in_Index) | ||||||||||
| if I drag it down .. It will result in like below… | ||||||||||
| =1+(Mod(1-1),3) | =1+0=1 | MOD(ROWS($F$1:F1)-1,3) | ||||||||
| =1+(Mod(2-1),3) | =1+1=2 | The bold part is the Column offset for OFFSET | ||||||||
| =1+(Mod(3-1),3) | =1+2=3 | alternative.. | ||||||||
| =1+(Mod(4-1),3) | =1+0=1 | |||||||||
| =1+(Mod(5-1),3) | =1+1=2 | |||||||||
| =1+(Mod(6-1),3) | =1+2=3 | |||||||||
| =1+(Mod(7-1),3) | =1+0=1 | |||||||||
| =1+(Mod(8-1),3) | =1+1=2 | |||||||||
| =1+(Mod(9-1),3) | =1+2=3 | |||||||||
| =1+(Mod(10-1),3) | =1+0=1 | |||||||||
| =1+(Mod(11-1),3) | =1+1=2 | |||||||||
| =1+(Mod(12-1),3) | =1+2=3 | |||||||||
| so we can conclude that we want an repeating incremental effect in number then we should use | ||||||||||
| MOD construction.. Because it returns the remainder of incrementing rows divided by No. of columns | ||||||||||
| So Index will do the rest … | ||||||||||
| =INDEX(Range, Row_No., Column_No.) | ||||||||||
| Dragged down… | ||||||||||
| Now comes the Second formula…(OFFSET) | ||||||||||
| =OFFSET($A$1,INT((ROWS($F$1:F1)-1)/3),MOD(ROWS($F$1:F1)-1,3)) | ||||||||||
| Base Cell | Row Offset | Column Offset | ||||||||
| Actually it is an contraction of INDEX formula.. | ||||||||||
| I have already explained the Row and Column offset parameters in the above tables… | ||||||||||
| so enjoy the beauty of this beautiful constructions.. | ||||||||||
| however, you can also use COLUMNS function to define No. of columns … | ||||||||||
Regards, | ||||||||||
File can be downloaded here..
Comments
Post a Comment