How can i copy cells as this orders?

i have a lot of data and need to put it like this example . I have two column A , B and need to copy A1 in Column C with value in B2 with the rest of column B which will have 10 value .. then repeat again until i finish
at the last row will be `A3` will be start with `B4 until B10` then `A4` with `B5 until B10` and so on .. until i finish and got `A9` with `B10`

Please Login or Register  to view this content.

the values of A10 and B1 will not be used
i attached an example for what i want .. but if it is possible can i do it with formula not VBA ? i searched for how to use index and offset but couldn't get the right result >>

I doubt if you will get a faster formula. I can generate the column A values with a formula... but can't yet get the column B values.

Well, for what it's worth, here is a formula. In D2, a Helper copied down:

Formula:

Please Login or Register  to view this content.

It will take some time to calculate on a big dataset... Hence you may prefer VBA!! Then in E2, copied down:

=IF(F2="","",INDEX($A$2:$A$7,D2))

and in F2, copied down:

=IFERROR(INDEX($B$3:$B$8,COUNTIF($D$2:D2,D2)+D2-1),"")

it might be slow... well, it WILL be slow.