I have 1 long column in an excel sheet. I want to split that 1 column into multiple columns.
The multiple column split should happen after every 10 entries. So if there are a total 80 cells in my original column I want them to be split into 8 columns each having 10 cells.
I am not well-versed in VBA so I won't be able to follow hints, but I might me able to modify a sample code.
My Question Begins Now
I have searched SU and found this topic How to Split one column in Excel into multiple columns of 55 rows
It has the following command
=INDIRECT("Sheet1!R" & (COLUMN()-1)*55+MOD(ROW()-1,55)+1 & "C1", FALSE)
I modified it to
=INDIRECT("Sheet1!R" & (COLUMN()-1)*10+MOD(ROW()-1,10)+1 & "C1", FALSE)
I understand the first parameter to INDIRECT should be the source where I want to pick the value from so it should be of the format "Sheet1!RnnnCnnn" where 'nnn' are the row and column numbers.
Can someone please explain what is happening in the following:
"Sheet1!R" & (COLUMN()-1)*10+MOD(ROW()-1,10)+1 & "C1"