Copy range of data based on starting cell content and end cell content

[S. Das]

Hi, I want an excel worksheet which will capable of doing such a thing where a long data range will be present in sheet 1, and in sheet 2, user will give the starting cell content and end cell content, and based on that the system has to copy all the data from sheet 1 to sheet2 in between them . I am attaching a sample file, I hope this will help you to understand my need in a better way.

Thanks in advance

 

[bosco_yip]

Try,

In "Student code" E2, formula copied down :

=IFERROR(0+TEXT(OFFSET(Sheet1!A$2,MATCH(LOOKUP(9^9,B$1:B2),OFFSET(Sheet1!A$2,,MATCH(LOOKUP("zz",A$1:A2),Sheet1!B$1:D$1,0),14),0)+MOD(ROW(A1)-1,6)-1,MATCH(LOOKUP("zz",A$1:A2),Sheet1!B$1:D$1,0)),"[>"&LOOKUP(9^9,C$1:C2)&"]\0;0"),"")

Regards
Bosco

 

[S. Das]

Respected Sir, Thank You so much for your support and quick reply
But I am unable to edit the code as per my need. Here I am attaching the original worksheet and in the sheet2 column E, J, O, T, Y of this worksheet I need your code to work.
Please help me.

 

[bosco_yip]

Respected Sir, Thank You so much for your support and quick reply
But I am unable to edit the code as per my need. Here I am attaching the original worksheet and in the sheet2 column E, J, O, T, Y of this worksheet I need your code to work.
Please help me.

Your new worksheet output layout is difference from post #1 file, but the formula logic is the same.

Try,

1] In E3, formula copied down to E27 :

=IFERROR(1/(1/TEXT(OFFSET(Sheet1!$A$4,MATCH(C$3,OFFSET(Sheet1!$A$4,,MATCH(B$3,Sheet1!$A$2:$N$2,0),850),0)+ROW(A1)-2,MATCH(B$3,Sheet1!$A$2:$N$2,0)),"[>"&D$3&"]\0;0")),"")

2] Then, copy E3:E27, paste to I3:I27, M3:M27, Q3:Q27 and U3:U27.

3] See revised (1) attachment.

Regards
Bosco

 

[S. Das]

thank you so much sir

 

[S. Das]

Dear sir, getting trouble again. Your code is working awesome for range 3 to 27 but when I want to use this code for range 28 to 52 it's not working. As the worksheet is for Examination Seat Plan System, range 3 to 27 is for room number 1 of my college (range 28 to 52 for room number 2, 53 to 77 for room number 3 like this) and I have total 30 rooms in my college where I need your code for each room.
After trying a lot to make it work, I again request you to help me.

 

[bosco_yip]

Dear sir, getting trouble again. Your code is working awesome for range 3 to 27 but when I want to use this code for range 28 to 52 it's not working. As the worksheet is for Examination Seat Plan System, range 3 to 27 is for room number 1 of my college (range 28 to 52 for room number 2, 53 to 77 for room number 3 like this) and I have total 30 rooms in my college where I need your code for each room.
After trying a lot to make it work, I again request you to help me.

1] The revised formula in E3 copied down :

=IF(ROWS($1:1)<=MATCH(9.9E+307,D:D)+22,IFERROR(1/(1/TEXT(OFFSET(Sheet1!$A$4,MATCH(LOOKUP(9.9E+307,C$3:C3),OFFSET(Sheet1!$A$4,,MATCH(B$3,Sheet1!$A$2:$N$2,0),850),0)+MOD(ROWS($1:1)-1,25)-1,MATCH(B$3,Sheet1!$A$2:$N$2,0)),"[>"&LOOKUP(9.9E+307,D$3:D3)&"]\0;0")),""),"")

2] and, copied right to other columns as per post #4

3] See revised (2) attachment.

Regards
Bosco

 

[S. Das]

Dear @bosco_yip one problem found. Your code is alright only when the subject is same for all the room, i.e, if a user enters physics as the first subject of room no 1 and same for room no 2 and so on then your code is ok. but if the user changes the first subject for room no 2 form physics to chemistry then your code give nothing.

And my request is if you modify the code then pls also consider 50 rows for a room that means for room no 1 range should be 3 to 52, room no 2 range should be 53 to 102 as it is also needed for another application.

Please help.