How to transfer values from one column to a big table?
 

Hi,

I have a column with over 200 000 rows. I need to transfer values from this
column to multiple columns so that values from Column A rows 1-4000 go to
Column B (rows 2-4001) , values from Column A rows 4001-8000 go to column C
(rows 2-4001), values from Column A rows 8001-12 000 go to column D (rows
2-4001) etc.

How to make this easily?

Thanks a lot for the help!

-Piia

How to transfer values from one column to a big table?
 

Put this in B2
=INDIRECT("A"&(ROW())+((COLUMN()-2)*4000))
copy it down to B4001 and across as needed

Once you are happy, copy and paste special as values to remove the formulas...

"Piia" wrote:

Hi,

I have a column with over 200 000 rows. I need to transfer values from this
column to multiple columns so that values from Column A rows 1-4000 go to
Column B (rows 2-4001) , values from Column A rows 4001-8000 go to column C
(rows 2-4001), values from Column A rows 8001-12 000 go to column D (rows
2-4001) etc.

How to make this easily?

Thanks a lot for the help!

-Piia

How to transfer values from one column to a big table?
 

Put this in cell B2
=OFFSET($A$1,ROW(A1)-1+(COLUMN(A1)-1)*4000,0)

Copy down 4000 rows, and across as desired/needed.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Piia" wrote:

Hi,

I have a column with over 200 000 rows. I need to transfer values from this
column to multiple columns so that values from Column A rows 1-4000 go to
Column B (rows 2-4001) , values from Column A rows 4001-8000 go to column C
(rows 2-4001), values from Column A rows 8001-12 000 go to column D (rows
2-4001) etc.

How to make this easily?

Thanks a lot for the help!

-Piia

How to transfer values from one column to a big table?
 

Since OP wants to go from row 1 to row 2 (offset of 1) need to modify to:
=INDIRECT("A"&(ROW()-1)+((COLUMN()-2)*4000))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Sheeloo" wrote:

Put this in B2
=INDIRECT("A"&(ROW())+((COLUMN()-2)*4000))
copy it down to B4001 and across as needed

Once you are happy, copy and paste special as values to remove the formulas...

"Piia" wrote:

Hi,

I have a column with over 200 000 rows. I need to transfer values from this
column to multiple columns so that values from Column A rows 1-4000 go to
Column B (rows 2-4001) , values from Column A rows 4001-8000 go to column C
(rows 2-4001), values from Column A rows 8001-12 000 go to column D (rows
2-4001) etc.

How to make this easily?

Thanks a lot for the help!

-Piia