Hi guys i wonder if you can help me out with this pls.
this sample below almost worked except the pattern of data is not compley constant ie some are 11 rows and some are 12.
https://youtu.be/BOQKWt8zAKs
Hi guys i wonder if you can help me out with this pls.
this sample below almost worked except the pattern of data is not compley constant ie some are 11 rows and some are 12.
https://youtu.be/BOQKWt8zAKs
(001) Please try this formula on your Example 2 tab.
[D7] =INDEX($A$1:$A$20,(ROW()-7)*(COUNTA($1:$1)-1)+COLUMN()-3)
Copy from D7 to D7:H10.
In order to adapt this formula to your other needs please learn how it works. At the core is a simple INDEX function. =INDEX([Range],[Row],[Column]). The range here is A1:A20. I would urge you to replace the reference with a named range which you can make dynamic. Entering this dynamism into this formula just clutters it up.
Since the Range has only a single column, the function's [Column] argument is omitted. Therefore all of(ROW()-7)*(COUNTA($1:$1)-1)+COLUMN()-3 just specifies a row number. ROW()-7 specifies the row in which the first formula resides which is D7, meaning row 7. We need this number to be 0. Therefore, if you paste the formula into D2 instead of D7 this part should read ROW()-2. =Row()-7, pasted into row 7 and copied down will count 0, 1, 2, 3 etc. 0*5, 1*5, 2*5 etx identifies the group from which to retrieve data.
It's almost the same for COLUMN()-3. We need this number to be 1 in column D. =COLUMN() returns the number of the column in which the formula resides. Therefore, if the formula is in column D it returns 4. If we want this number to be 1 we must subtract 3. Therefore COLUMN()-3 when the formula is in column D but Column()-1 if it were in column B.
=Column()-3 returns 1 when entered in column D and counts from 1 up as you copy this formula to the right. That's how the cells A1, A2,A3 etc. are addressed.
In your Example 2 sheet the items in column A are in groups of 5. Therefore the first name is in Cell1 of the Range or (0*5)+1. The next one is in cell (1*5)+1, then (2*5)+1 etc. I figured you would need 5 captions for your 5 columns and that would be 3 captions only if your data would have groups of 3, 12 captions if your data had groups of 12. I inserted the 5 captions in the range D1:H1. Now COUNTA($1:$1) gives me the number of columns plus 1 for A1 which is not a caption. Therefore you find COUNTA($1:$1)-1 in the formula as the multiplicator for the row number. Of course, there are other ways of using the COUNTA() function to get the number of items in the group. Modify the formula as needed.