How to convert a single column into multiple columns?

RITESH · June 15, 2021, 2:03pm

I have 640000 numbers in one column, I want this column to break into 640000/128 = 5000 columns. i.e. each column will contain 128 numbers. The first column will contain 1:128, then the second column 129:256, and so on. Please help.
image description

anon73440385 · June 15, 2021, 2:19pm

You tagging is ambiguous - tag common means your question is supposed to be related to all modules of LibreOffice (i.e. to calc, writer, impress, draw) which all have “tables”. I’d assume your question is about Calc - correct assumption?

Anyway: `640000/128 = 5000` columns cannot be covered by a **default** Calc sheet, since the maximum number of columns of a default (i.e. without taking additional measures) Calc sheet is `1024`. You need to activate

experimental features (Tools -> Options... -> Advanced -> Option: [x] Enanble experimental features (my be unstable)) and
very large spreadsheets (Tools -> Options... -> LibreOffice Calc -> Defaults -> Option: [x] Enable very large spreadsheets (16, rows, 16384 cols))

RITESH · June 15, 2021, 2:41pm

Okay, I didn’t know about the common. New here. Let I want to convert the single column of numbers into 500 columns. How to do that?

anon73440385 · June 16, 2021, 10:29am

See the 5ct guess in my answer - your screenshot has an empty column, while my answer stated:

the first number is in cell A1 and
… into cell B1

So you are asking for a 100% correct solution, without providing 100% of details and not willing to understand the principle of an answer. Sorry - I’ll leave this question now.

anon73440385 · June 15, 2021, 3:03pm

Hello,

assuming

640000 numbers are in column A
the first number is in cell A1
you want 500 columns (which corresponds to 1280 values per column)

do the following (make a backup copy of your file in advance)

Enter formula =OFFSET($A$1;(COLUMN()-2)*1280+(ROW()-1);0;1;1) into cell B1
Copy formula
Enter B1:SG1280 into the Name Box (left of the formula bar) and press ENTER (640000 cells should be selected)
Paste copied formula using CTLR+V
Optional Enter B1:SG1280 into the Name Box and use menu Data -> Calculate -> Formula to value and delete column A

Tested using LibreOffice:

Version: 7.1.4.2 / LibreOffice Community
Build ID: a529a4fab45b75fefc5b6226684193eb000654f6
CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: kf5
Locale: en-US (en_US.UTF-8); UI: en-US, Calc: threaded

Hope that helps.

RITESH · June 16, 2021, 10:11am

Not working sir

anon73440385 · June 16, 2021, 10:21am

Not working sir

Do you think that I provided the test information just for the sake of fake? And “Not working” isn’t a error description, hence I can’t help you further. My 5ct guess: One or more of the 3 assumptions in my answer is/are not fulfilled and you did not adapt the formula according to where your data really reside in your case. Try to understand the idea of the solution instead of copy&paste without wasting any thoughts about the formula.

EarnestAl · June 18, 2021, 2:31am

Maybe it should read Copy Cell (B1), rather than “Copy formula”? Cheers, Al