3

We all know the standard filter, where you pick which rows to show depending on values in a particular column (or columns). Is it at all possible to the the same, but picking which columns should be visible depending on the values of a single row? The only ideas I have so far is to either write a macro or do it manually.

Ister
  • 73
  • 1
  • 2
  • 7

2 Answers2

5

Can not post comment yet, so:

Can you do normal excel operations in your tool like:

copy the data area and pastespecial as transpose and then apply filter to columns.

ZAT
  • 1,347
  • 7
  • 10
  • A good solution, one just need to be aware of its limitations (tested only under LibreOffice): Conditional formatting is lost; Formulas with $ does not transpose correctly (e.g. B$3 will change into something strange, definitely not $C2 as it should). Still, can be used as a workaround, eventually with some additional work. Thanks! – Ister Oct 16 '14 at 08:11
  • Glad its helped. Dont know about Libreoffice transpose issue. By the way, now i can post comment. – ZAT Oct 16 '14 at 08:59
  • Of course the 'paste special' is a one-timer. But there is also a function `=TRANSPOSE(...)`. Make sure to insert it as a matrix function (checkbox in wizard, or Ctrl-Enter from cell-edit. – Yahoo Serious Dec 22 '16 at 19:17
0

As for the macro, I've put together a small helper extension that allows one to write python code straight into cells - check it at https://github.com/jsbueno/librepylot/releases/tag/0.7

After installing it, some code like the following in a cell could do what you want:

for col in range(0, 26): #A - Z
   S[0]._sheet.Columns.getByIndex(col)  =  bool(S[0][col, 0]._cell.getValue())

Above the first "0" in S[0] is the sheet number, the second "0" in S[0][col,0] is the index of the line with your values (line 1) here I just set the visibility based ont he truthness (!= 0) of the cell computed value. Use whatever python expression you want

These two lines of code should be put in as the text contents of a cell - taking care that the second line is indented, and that libreoffice does not change the first letters of any command to uppercase (and preventing other automatic conversions it does by default) A second cell should be written with the formula =pyexec(B1) (assuming the code is in cell b1) - Whenever you want to "rerun" hte filter, just edit either of the two cells.

jsbueno
  • 99,910
  • 10
  • 151
  • 209
  • I've only tested this in libreoffice on linux - please report any issues on github if you give it a try,. – jsbueno Oct 02 '14 at 19:19