Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 17 Nov 2017 19:17:26 +0100paste numbers into 4 columnshttps://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/I want to take a column of 120 or so numbers from a spreadsheet and enter them in another spreadsheet in 4 columns.
So, the first 4 numbers would be side by side in row 1 and the second 4 numbers would be in row 2.
I have a workaround that is doable but labor intensive. I can highlight the first and 5th and 9th etc. numbers until I get to 117, and then Copy or Cut and then Paste into the new spreadsheet.
And repeat 3 more times.
I think there should be someone out there who has already figured this out.
If not, maybe I'll need to learn how to write a Macro.Thu, 09 Nov 2017 19:05:51 +0100https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/Comment by joeh6966 for <p>I want to take a column of 120 or so numbers from a spreadsheet and enter them in another spreadsheet in 4 columns.
So, the first 4 numbers would be side by side in row 1 and the second 4 numbers would be in row 2.
I have a workaround that is doable but labor intensive. I can highlight the first and 5th and 9th etc. numbers until I get to 117, and then Copy or Cut and then Paste into the new spreadsheet.
And repeat 3 more times.
I think there should be someone out there who has already figured this out.
If not, maybe I'll need to learn how to write a Macro.</p>
https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=138270#post-id-138270I looked there but thought that it would be commenting on the comment about commas and semi-colons.Fri, 17 Nov 2017 15:59:48 +0100https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=138270#post-id-138270Comment by joeh6966 for <p>I want to take a column of 120 or so numbers from a spreadsheet and enter them in another spreadsheet in 4 columns.
So, the first 4 numbers would be side by side in row 1 and the second 4 numbers would be in row 2.
I have a workaround that is doable but labor intensive. I can highlight the first and 5th and 9th etc. numbers until I get to 117, and then Copy or Cut and then Paste into the new spreadsheet.
And repeat 3 more times.
I think there should be someone out there who has already figured this out.
If not, maybe I'll need to learn how to write a Macro.</p>
https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137540#post-id-137540I didn't see it yet. Just now I hit the refresh button in my URL field and got these messages.Thu, 09 Nov 2017 23:19:02 +0100https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137540#post-id-137540Comment by Jim K for <p>I want to take a column of 120 or so numbers from a spreadsheet and enter them in another spreadsheet in 4 columns.
So, the first 4 numbers would be side by side in row 1 and the second 4 numbers would be in row 2.
I have a workaround that is doable but labor intensive. I can highlight the first and 5th and 9th etc. numbers until I get to 117, and then Copy or Cut and then Paste into the new spreadsheet.
And repeat 3 more times.
I think there should be someone out there who has already figured this out.
If not, maybe I'll need to learn how to write a Macro.</p>
https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137619#post-id-137619You're welcome!
The way to thank someone is to mark the answer as correct, which you have done, and consider upvoting the answer (maybe you did this too; it's anonymous so I'm not sure). Optionally add a comment, which you have also done. It would have been more proper to comment on the answer, rather than the question, but that's a minor point.Fri, 10 Nov 2017 20:12:05 +0100https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137619#post-id-137619Comment by Lupp for <p>I want to take a column of 120 or so numbers from a spreadsheet and enter them in another spreadsheet in 4 columns.
So, the first 4 numbers would be side by side in row 1 and the second 4 numbers would be in row 2.
I have a workaround that is doable but labor intensive. I can highlight the first and 5th and 9th etc. numbers until I get to 117, and then Copy or Cut and then Paste into the new spreadsheet.
And repeat 3 more times.
I think there should be someone out there who has already figured this out.
If not, maybe I'll need to learn how to write a Macro.</p>
https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137538#post-id-137538What was wrong with the formula @Jim K posted? It works flawlessly for the pupose. You won't find a ready-made tool for the very specific proceeding.Thu, 09 Nov 2017 23:11:54 +0100https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137538#post-id-137538Comment by joeh6966 for <p>I want to take a column of 120 or so numbers from a spreadsheet and enter them in another spreadsheet in 4 columns.
So, the first 4 numbers would be side by side in row 1 and the second 4 numbers would be in row 2.
I have a workaround that is doable but labor intensive. I can highlight the first and 5th and 9th etc. numbers until I get to 117, and then Copy or Cut and then Paste into the new spreadsheet.
And repeat 3 more times.
I think there should be someone out there who has already figured this out.
If not, maybe I'll need to learn how to write a Macro.</p>
https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137541#post-id-137541It does work flawlessly! It is the answer to my needs. I couldn't have ever figured out how to write that equation. Awesome!Thu, 09 Nov 2017 23:37:05 +0100https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137541#post-id-137541Comment by joeh6966 for <p>I want to take a column of 120 or so numbers from a spreadsheet and enter them in another spreadsheet in 4 columns.
So, the first 4 numbers would be side by side in row 1 and the second 4 numbers would be in row 2.
I have a workaround that is doable but labor intensive. I can highlight the first and 5th and 9th etc. numbers until I get to 117, and then Copy or Cut and then Paste into the new spreadsheet.
And repeat 3 more times.
I think there should be someone out there who has already figured this out.
If not, maybe I'll need to learn how to write a Macro.</p>
https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137542#post-id-137542I didn't see until 10 minutes ago. It does work flawlessly. Thanks @Jim K.
I do not see where I can thank him except in these Comment boxes.Thu, 09 Nov 2017 23:46:25 +0100https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137542#post-id-137542Answer by Lupp for <p>I want to take a column of 120 or so numbers from a spreadsheet and enter them in another spreadsheet in 4 columns.
So, the first 4 numbers would be side by side in row 1 and the second 4 numbers would be in row 2.
I have a workaround that is doable but labor intensive. I can highlight the first and 5th and 9th etc. numbers until I get to 117, and then Copy or Cut and then Paste into the new spreadsheet.
And repeat 3 more times.
I think there should be someone out there who has already figured this out.
If not, maybe I'll need to learn how to write a Macro.</p>
https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?answer=137547#post-id-137547There is also a way with less and simpler fomulas: Paste the copied column 4 times in adjacent columns, the first one starting at row five, the next one to the right one row higher and so on. Above the first element in the **last** column you have one empty cell now. Right of the first element in the last <s>row</s> column enter the formula <s>`=(MOD(ROW()-1;1)=0)`</s> {acting on the comment below; correction of the typo:} `=(MOD(ROW()-1;4)=0)` and fill it down for all the non-empty rows. Now select this column completely and choose the 'Autofliter' from 'Data'. From the dropdown of the filter select TRUE. If you now copy what not is filtered out, you can paste it elsewhere to get the wanted result. Hmmm... Lot of handiwork... (Just for completeness, kind of.)Fri, 10 Nov 2017 00:57:44 +0100https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?answer=137547#post-id-137547Comment by joeh6966 for <p>There is also a way with less and simpler fomulas: Paste the copied column 4 times in adjacent columns, the first one starting at row five, the next one to the right one row higher and so on. Above the first element in the <strong>last</strong> column you have one empty cell now. Right of the first element in the last <s>row</s> column enter the formula <s><code>=(MOD(ROW()-1;1)=0)</code></s> {acting on the comment below; correction of the typo:} <code>=(MOD(ROW()-1;4)=0)</code> and fill it down for all the non-empty rows. Now select this column completely and choose the 'Autofliter' from 'Data'. From the dropdown of the filter select TRUE. If you now copy what not is filtered out, you can paste it elsewhere to get the wanted result. Hmmm... Lot of handiwork... (Just for completeness, kind of.)</p>
https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=138299#post-id-138299I tried it again with your changes, and it does work. It's very interesting how different approaches can yield the same result. Thanks.Fri, 17 Nov 2017 19:17:26 +0100https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=138299#post-id-138299Comment by joeh6966 for <p>There is also a way with less and simpler fomulas: Paste the copied column 4 times in adjacent columns, the first one starting at row five, the next one to the right one row higher and so on. Above the first element in the <strong>last</strong> column you have one empty cell now. Right of the first element in the last <s>row</s> column enter the formula <s><code>=(MOD(ROW()-1;1)=0)</code></s> {acting on the comment below; correction of the typo:} <code>=(MOD(ROW()-1;4)=0)</code> and fill it down for all the non-empty rows. Now select this column completely and choose the 'Autofliter' from 'Data'. From the dropdown of the filter select TRUE. If you now copy what not is filtered out, you can paste it elsewhere to get the wanted result. Hmmm... Lot of handiwork... (Just for completeness, kind of.)</p>
https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137588#post-id-137588I got as far as the formula into the cell right of the last row. When I filled it down, the whole column said "TRUE". And then nothing happened using Autofilter.Fri, 10 Nov 2017 16:37:39 +0100https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137588#post-id-137588Comment by Lupp for <p>There is also a way with less and simpler fomulas: Paste the copied column 4 times in adjacent columns, the first one starting at row five, the next one to the right one row higher and so on. Above the first element in the <strong>last</strong> column you have one empty cell now. Right of the first element in the last <s>row</s> column enter the formula <s><code>=(MOD(ROW()-1;1)=0)</code></s> {acting on the comment below; correction of the typo:} <code>=(MOD(ROW()-1;4)=0)</code> and fill it down for all the non-empty rows. Now select this column completely and choose the 'Autofliter' from 'Data'. From the dropdown of the filter select TRUE. If you now copy what not is filtered out, you can paste it elsewhere to get the wanted result. Hmmm... Lot of handiwork... (Just for completeness, kind of.)</p>
https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137604#post-id-137604Sorry! There was a typo. The formula should say TRUE in every fourth row. starting with row 5..
ROW()-1 returns 4, 5, 6, 7, 8 ,9 and so on then, row by row. It's one of the rows where a valid group of 4 consecutive elements is shown exactly if the number returned is divisible by 4 without a remainder. The MOD function is made for this: MOD(SomeInt; SomeIntDivisor) returns the remainder you get if you divide SomeInt by SomeIntDivisor. Mod(N;1)=0 is always TRUE, of course.Fri, 10 Nov 2017 18:28:22 +0100https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137604#post-id-137604Answer by Jim K for <p>I want to take a column of 120 or so numbers from a spreadsheet and enter them in another spreadsheet in 4 columns.
So, the first 4 numbers would be side by side in row 1 and the second 4 numbers would be in row 2.
I have a workaround that is doable but labor intensive. I can highlight the first and 5th and 9th etc. numbers until I get to 117, and then Copy or Cut and then Paste into the new spreadsheet.
And repeat 3 more times.
I think there should be someone out there who has already figured this out.
If not, maybe I'll need to learn how to write a Macro.</p>
https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?answer=137528#post-id-137528Enter the following formula in cell B1.
=INDIRECT(ADDRESS((ROW()-1)*4+COLUMN()-1;1))
Then drag to fill over to E1, then down as far as needed.
![result](/upfiles/15102564544434739.jpg)
Finally, cut from columns B through E and paste special as values into the new spreadsheet.Thu, 09 Nov 2017 20:42:01 +0100https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?answer=137528#post-id-137528Comment by joeh6966 for <p>Enter the following formula in cell B1.</p>
<pre><code>=INDIRECT(ADDRESS((ROW()-1)*4+COLUMN()-1;1))
</code></pre>
<p>Then drag to fill over to E1, then down as far as needed.</p>
<p><img alt="result" src="/upfiles/15102564544434739.jpg"></p>
<p>Finally, cut from columns B through E and paste special as values into the new spreadsheet.</p>
https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=138271#post-id-138271Thanks!
BTW, shouldn't the last word in Lupp's comment be "comma"?Fri, 17 Nov 2017 16:00:40 +0100https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=138271#post-id-138271Comment by Lupp for <p>Enter the following formula in cell B1.</p>
<pre><code>=INDIRECT(ADDRESS((ROW()-1)*4+COLUMN()-1;1))
</code></pre>
<p>Then drag to fill over to E1, then down as far as needed.</p>
<p><img alt="result" src="/upfiles/15102564544434739.jpg"></p>
<p>Finally, cut from columns B through E and paste special as values into the new spreadsheet.</p>
https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137539#post-id-137539A majority of locales cannot use the comma as the parameter delimiter because it conflicts with the decimal separator. The semicolon is still accepted globally by all locales. For some time now it got replaced for the display by a comma in locales using the point as the decimal separator. An ingenious idea to mess up international cooperation in forums.
But you will not be blamed if you continue to post formulas with the semicolon.Thu, 09 Nov 2017 23:14:23 +0100https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137539#post-id-137539Comment by Jim K for <p>Enter the following formula in cell B1.</p>
<pre><code>=INDIRECT(ADDRESS((ROW()-1)*4+COLUMN()-1;1))
</code></pre>
<p>Then drag to fill over to E1, then down as far as needed.</p>
<p><img alt="result" src="/upfiles/15102564544434739.jpg"></p>
<p>Finally, cut from columns B through E and paste special as values into the new spreadsheet.</p>
https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137616#post-id-137616@Lupp: Oops, thanks; I knew that but forgot to change it before posting.Fri, 10 Nov 2017 19:49:24 +0100https://ask.libreoffice.org/en/question/137524/paste-numbers-into-4-columns/?comment=137616#post-id-137616