| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Pivot Table - Counting Unique Values within one column of da | Excel Worksheet Functions | |||
| Show all values in first column of Pivot Table | Excel Discussion (Misc queries) | |||
| Pivot Table - Changing Column Values | Excel Discussion (Misc queries) | |||
| summing values in a data table based on criteria in another column | Excel Worksheet Functions | |||
| assigning value to a column based on a table of values | Excel Discussion (Misc queries) | |||
Linear Mode
