Forum Discussion
Divide each 1000 records in a large sheet to separate sheets
Can you help me, how to split each 1000 records in Excel and move them to separate sheets? Because Oracle does not accept more than 1000 record in a query. Thanks
Hello! There is a whole range of possibilities for your task. I would like to show you some of them here. Assuming your data is in Data!A1:D5000 then you could generate the first 1000 data with the following formulas:
=INDEX(Data!A1:D5000,SEQUENCE(1000),{1,2,3,4})
or
=INDEX(Data!A1:D5000,SEQUENCE(1000),SEQUENCE(1,4))
For the second 1000 lines, you must then adapt SEQUENCE():
=INDEX(Data!A1:D5000,SEQUENCE(1000,,1001),{1,2,3,4})
or
=INDEX(Data!A1:D5000,SEQUENCE(1000,,1001),SEQUENCE(1,4))
Alternatively, if you can't use SEQUENCE() yet:
=INDEX(Data!A1:D5000,ROW(1:1001),{1,2,3,4})
or
=INDEX(Data!A1:D5000,ROW(1:1001),COLUMN(A:D))
A rather new function is CHOOSEROWS():
=CHOOSEROWS(Data!A1:D5000,SEQUENCE(1000))
I still have one.
=FILTER(Data!A1:D5000,(SEQUENCE(5000)>=1)*(SEQUENCE(5000)<=1001),"")