Reverse columns order in libre office calc

Question

Suppose I have This This Cells:

And then for some reason I want to reverse the order of columns automatically to become something like This:

any help will be appreciated!

How is this question related to programming? Insert a new empty column `A` left before old column `A`. Cut/Paste the now last column `D` therein. Now Cut/Paste the column `B` to the now empty column `D`. Delete the now empty column `B`. — Axel Richter, Feb 04 '17 at 07:39
@AxelRichter Thank you for your comment. But I think there is a misunderstanding. This 3 columns are just an example. I want to do this for large number of columns. So Cut and Paste is not What I looking for. This should probably via programming. — SirSaleh, Feb 04 '17 at 10:15
Any script that can use in libre-office calc. Sorry I don't know what programming tools Libre-office calc has in itself! — SirSaleh, Feb 04 '17 at 12:24

score 9 · Accepted Answer · answered Aug 01 '18 at 08:36

9

quick steps:

insert new row above 1
fill row with monoton increasing integer index to the right.
Then select your data and sort descending, so highest indexed right col is first
copy and paste the result

https://gr-plus.blogspot.com/2015/01/reverse-column-or-row-order-in-excel-or.html

answered Aug 01 '18 at 08:36

droid192

2,011
26
43

My question is belong to long time ago! but wise solution. thanks. :)) – SirSaleh Aug 01 '18 at 14:45
That's genius (provided there aren't too many entries). – Clément Jun 03 '23 at 16:25

score 1 · Answer 2 · answered Feb 04 '17 at 15:10

So here is an approach using Libreoffice Basic and the Libreoffice API.

sub ReverseColumns()

 oThisWorkbook = ThisComponent
 oActiveSheet = oThisWorkbook.CurrentController.ActiveSheet

 oRow1 = oActiveSheet.getRows().getByIndex(0)
 aFilledCellsRow1 = oRow1.queryContentCells(1+2+4+16).getRangeAddresses()

 if ubound(aFilledCellsRow1) = -1 then exit sub

 lLastFilledColumnRow1 = aFilledCellsRow1(ubound(aFilledCellsRow1)).EndColumn

 c = 0
 for i = lLastFilledColumnRow1 to 1 step -1
  oCellTargetColumn = oActiveSheet.getCellByPosition(c, 0)
  oRangeAddressTargetColumn = oCellTargetColumn.RangeAddress
  oActiveSheet.insertCells(oRangeAddressTargetColumn, com.sun.star.sheet.CellInsertMode.COLUMNS)

  oCellTargetColumn = oActiveSheet.getCellByPosition(c, 0)
  oCellAddressTargetColumn = oCellTargetColumn.CellAddress

  oRangeSource = oActiveSheet.Columns.getByIndex(lLastFilledColumnRow1 + 1)
  oRangeAddressSource = oRangeSource.RangeAddress
  oActiveSheet.moveRange(oCellAddressTargetColumn, oRangeAddressSource)
  c = c + 1
 next

end sub

This first determines the last filled column in row 1. The column reversing process will then be done until that column.

For learning about Macros in Libreoffice start here: https://wiki.documentfoundation.org/Macros

I don't know where I do that wrong that This Macro stops with for me `BASIC syntax error. Expected: Sub.` — SirSaleh, Feb 05 '17 at 11:05
The error means, the keyword `Sub` is expected but not found. You have seen my code starts with `sub ReverseColumns()` in first line? — Axel Richter, Feb 05 '17 at 11:22
Yes! And I check it in my code! I have it also, But the error exists yet. — SirSaleh, Feb 05 '17 at 11:28
Within the Module where the code is stored in, all code must be compilable since it all will be compiled before runned.. So the best would be to make sure, there is only the provided code in this Module, nothing before the `sub ReverseColumns()` except `REM`arks and nothing after the `end sub`. If more code is in the module, all code must be inside `Sub ... End Sub` or `FUNCTION ... END FUNCTION` except `REM`arks or global definitions (`DIM ...`, `GLOBAL ...`, `PUBLIC ...`, `PRIVATE ...`, ...). — Axel Richter, Feb 05 '17 at 11:56

score 0 · Answer 3 · edited May 23 '17 at 11:47

In case anyone here wants to reverse the order of rows (instead of columns) ... I took the macro code posted by @Axel-Richter and edited it so it does just that:

sub ReverseRows()

  oThisWorkbook = ThisComponent
  oActiveSheet = oThisWorkbook.CurrentController.ActiveSheet

  oColumn1 = oActiveSheet.getColumns().getByIndex(0)
  aFilledCellsColumn1 = oColumn1.queryContentCells(1+2+4+16).getRangeAddresses()

  if ubound(aFilledCellsColumn1) = -1 then exit sub

  lLastFilledRowColumn1 = aFilledCellsColumn1(ubound(aFilledCellsColumn1)).EndRow

  c = 0

  for i = lLastFilledRowColumn1 to 1 step -1
    oCellTargetRow = oActiveSheet.getCellByPosition(0, c)
    oRangeAddressTargetRow = oCellTargetRow.RangeAddress
    oActiveSheet.insertCells(oRangeAddressTargetRow, com.sun.star.sheet.CellInsertMode.ROWS)

    oCellTargetRow = oActiveSheet.getCellByPosition(0, c)
    oCellAddressTargetRow = oCellTargetRow.CellAddress

    oRangeSource = oActiveSheet.Rows.getByIndex(lLastFilledRowColumn1 + 1)
    oRangeAddressSource = oRangeSource.RangeAddress
    oActiveSheet.moveRange(oCellAddressTargetRow, oRangeAddressSource)
    c = c + 1
  next

end sub

score 0 · Answer 4 · answered Dec 21 '21 at 15:51

0

Unfortunately the first answer does not work as it does not sort column by column but each column on its own.

You do not reverse the order of the columns but get the highest to lowest value of each column. Tried it just now. In old excel you could do a transpose trick but the same function is not available in libre office in 2021

answered Dec 21 '21 at 15:51

Lost

1

Please, if you think the selected answer does't work, edit your answer inserting your suggested solution – robertobatts Dec 21 '21 at 16:00
This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/30643873) – João Dias Dec 21 '21 at 23:21

score 0 · Answer 5 · edited Dec 22 '21 at 15:04

A better solution in 2021 is the INDEX- function.

=INDEX($A$1:$NN$25;1+$A30;25-A$30)

It takes the defined range of data you have, in a locked grid with dollar signs and takes the first row minus a value from two lists in increasing numbers.

1,2,3...in rows and 1,2,3 in columns. Locking the column in one instance, or the row in the other instance does the trick. Below you see a index table example with the stackoverflow ASCII formatting.

| A30  |    B30                             |            C30                    |

| ---- |----------------------------------- |-----------------------------------|

| A31  | =INDEX($A$1:$NN$25;1+$A30;25-A$30  | =INDEX($A$1:$NN$25;1+$A30;25-B$30 |

| A32  | =INDEX($A$1:$NN$25;1+$A31;25-A$30  | =INDEX($A$1:$NN$25;1+$A30;25-B$30 |

score 0 · Answer 6 · answered May 03 '23 at 17:52

Here's the method droid192 suggested, but with some useful pics. I had the same issue and my data had a column with 2100 entries. Doing that by hand was not going to happen.

First if you use a regular ascending or descending sort you actually sort the data not just flip it and using the right click paste-special and transpose option isn't quite right either.

So, make a copy of your column and paste it away from everything else. Next to that column create a numeric series from 1 to however many cells you have in your column (type 1 then 2 then 3 then drag all the way down.)

Now select and high-light the new column and click the sort in descending order button and choose expand selection and choose your column you want flipped.

Now its flipped. Remove the other column and past your new column where you need it.

Reverse columns order in libre office calc

6 Answers6