mind blowing Haseeb, On Sat, Oct 29, 2011 at 5:52 AM, Haseeb Avarakkan < haseeb.avarak...@gmail.com> wrote:
> Hello XLS S; > > Let me try to explain my best :) > > In sheet2 B1, used a formula to get all items in sheet1, which is > > =COUNTA(Source!A3:Z65536) > > B2, used too get total columns used, which is > > =COUNTA(Source!A2:Z2) > > Based on the attached, there are 3 columns & 300 rows. So, first 3 rows > need the data from row 1 in sheet1, but different columns. Next 3 rows from > Row2 > > Row1_ColA > Row1_ColB > Row1_ColC > Row2_ColA > Row2_ColB > Row2_ColC > > So, the logic is after 3 rows Row # needs to increase 1, Column # needs to > add 1 on each row, after 3 rows it needs to reset to 1 > > INT will rounddown a value to Integral. say, 2.99999 will round to 2, > 0.0001 to 0, 3.5 to 3 etc.... > > A5 formula > > > =INDEX(Source!$A$3:$Z$65536,INT((ROWS(A$5:A5)-1)/$B$2)+1,MOD(ROWS(A$5:A5)-1,$B$2)+1) > > (ROWS($A5:A5)-1) *| *How may rows in A5:A5 = 1, 1-1 > = 0 > (ROWS($A5:A5)-1)/$B$2 *|* 0/3 = 0 > INT((ROWS(A$5:A5)-1)/$B$2)+1 *|* INT(0)+1, 0+1 = 1 > > MOD function will returns the remainder after number is divided by divisor. > > eg: > > =MOD(3,6), multiplication of 6 where falls <=3, here it returns 1. If > number is less than the divisor, MOD will return the same number. > =MOD(20,6), multiplication of 6 where falls <=20, here it falls on 18 > (3*6), So the remaining value is 2. MOD will return 2 > =MOD(25,5) multiplication of 5 where falls <=25, here it falls on 25 > (5*5), So the remaining value is 0. MOD will return 0 > > In the attached, MOD(ROWS(A$5:A5)-1,$B$2)+1 > > ROWS($A5:A5)-1 *| *How may rows in A5:A5 = 1, 1-1 = 0 > MOD(0,3)+1 *|* 0+1 = 1 > > So the INDEX will become, INDEX(Source!$A$3:$Z$65536,1,1) > > A15, formula will be like > > > =INDEX(Source!$A$3:$Z$65536,INT((ROWS(A$5:A15)-1)/$B$2)+1,MOD(ROWS(A$5:A15)-1,$B$2)+1) > > INT((ROWS(A$5:A15)-1)/$B$2)+1 *|* INT(10/3)+1, > INT(3.33333333333333)+1, INT(3)+1, 3+1 = 4 > MOD(ROWS(A$5:A15)-1,$B$2)+1 *|* MOD(10,3)+1, > MOD(1)+1, 1+1 = 2 > > So the INDEX will become, > > INDEX(Source!$A$3:$Z$65536,4,2) > > If you use Formula Evaluator tool, you can see the calculation easily step > by step than my longest explanation :) > > HTH > Haseeb > > -- > FORUM RULES (925+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will > not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > > To post to this group, send email to excel-macros@googlegroups.com > -- ......................... -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
