Records re-allocation problem.
Dear all,
Theres a table A in a sheet in excel, in this table ,the records are placed row byrow,for example,if there are 20 records,then it occupies 1-21 rows(including column heading.) And I have to use the records in another table B,but the pattern of table B is for every four rows ,the first equals the one row in table A,and the other three rows are kept null,for example,(the second row in table B= the second row in table A;the sixth row in table B= the third row in table A;the tenth row in table B= the forth row in table A;€¦etc) How can I relize it?Thanks. Best regards. Dawn Wu Shanghai,China |
Records re-allocation problem.
Some thoughts to realize it ..
Suppose table A's data is within A2:C21 Place in say, E2: =IF(MOD(ROWS($1:1)-1,4)=0,OFFSET(A$2,INT((ROWS($1:1)-1)/4),),"") Copy E2 across to G2, then fill down as far as required to exhaust the extract from table A. What you'd get should be the desired results that you seek: 1st row of data from table A, then 3 blank rows, then the 2nd row of data from table A, then 3 blank rows, and so on. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dawn" wrote: Dear all, Theres a table A in a sheet in excel, in this table ,the records are placed row byrow,for example,if there are 20 records,then it occupies 1-21 rows(including column heading.) And I have to use the records in another table B,but the pattern of table B is for every four rows ,the first equals the one row in table A,and the other three rows are kept null,for example,(the second row in table B= the second row in table A;the sixth row in table B= the third row in table A;the tenth row in table B= the forth row in table A;€¦etc) How can I relize it?Thanks. Best regards. Dawn Wu Shanghai,China |
Records re-allocation problem.
=INDIRECT("TableA!"&ADDRESS(FLOOR(ROW()/4,1)+IF(ROW()=1,1,2),1,4,1))
returns the required row of column A =INDIRECT("TableA!"&ADDRESS(FLOOR(ROW()/4,1)+IF(ROW()=1,1,2),2,4,1)) of column B, etc. Regards, Stefi €žDawn€ť ezt Ă*rta: Dear all, Theres a table A in a sheet in excel, in this table ,the records are placed row byrow,for example,if there are 20 records,then it occupies 1-21 rows(including column heading.) And I have to use the records in another table B,but the pattern of table B is for every four rows ,the first equals the one row in table A,and the other three rows are kept null,for example,(the second row in table B= the second row in table A;the sixth row in table B= the third row in table A;the tenth row in table B= the forth row in table A;€¦etc) How can I relize it?Thanks. Best regards. Dawn Wu Shanghai,China |
All times are GMT +1. The time now is 10:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com