Oct 14 2022 06:44 PM
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
Oct 15 2022 04:07 AM - edited Oct 15 2022 04:09 AM
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),"")