calc filter?

users
#1

HI all,
im working on a calc sheet where i have a list of 16 names 1 to 16 B2 TO B17
I lie to filter the list in this order
1
16
8
9
4
13
5
12
2
15
7
10
3
14
6
11

thanks beau

#2

Hi,
My understanding is you want to re-display the list in the order you
provided. If you can calculate the odd indices (the even numbered ones
are always equal to the number of elements minus the odd index plus 1
in your list), replace the number in the sequence by that calculation.
Otherwise, you can place the index directly in the formula. To show the
list in the order you specified, you can use the following, in a
location of your choice, one line per row:
=indirect(address(row($b$2)+1-1;column($b$2)))
=indirect(address(row($b$2)+16-1;column($b$2)))
=indirect(address(row($b$2)+8-1;column($b$2)))
=indirect(address(row($b$2)+9-1;column($b$2)))
=indirect(address(row($b$2)+4-1;column($b$2)))
=indirect(address(row($b$
2)+13-1;column($b$2)))
=indirect(address(row($b$2)+5-1;column($b$2)))
=in
direct(address(row($b$2)+12-1;column($b$2)))
=indirect(address(row($b$2)
+2-1;column($b$2)))
=indirect(address(row($b$2)+15-1;column($b$2)))
=indi
rect(address(row($b$2)+7-1;column($b$2)))
=indirect(address(row($b$2)+10
-1;column($b$2)))
=indirect(address(row($b$2)+3-1;column($b$2)))
=indirec
t(address(row($b$2)+14-1;column($b$2)))
=indirect(address(row($b$2)+6
-1;column($b$2)))
=indirect(address(row($b$2)+11-1;column($b$2)))
If your data is displayed on another page, then you can use the page name in the ADDRESS function.
I hope this helps.
Regards,
Rémy Gauthier.

#3

Filtering in a spreadsheet arranges that only some rows are displayed and possibly that only these are included in calculations. Filtering does not sort material, so you cannot "filter in an order".

If you want the items rearranged in some particular - apparently fixed - order, why not simply enter or arrange them in that order manually? You do know how to move rows?

You can adjust the order of sorting by defining your own sort order at Tools | Options... | LibreOffice Calc | Sort Lists.

You could add an additional column in your data indicating the rank of each existing item. (This column could be hidden or excluded from the print range if desired.) So with your example, row 2 in the new column would have 1, because you want that row to be first, row 3 would have 9, as you want it in ninth place, row 4 would have 13, and so on. You could then sort your data (if that is indeed what you want to do) using the new column as the sort key.

An alternative is to decide exactly why you want the material in a different order. It may well be possible to perform whatever calculations you need with the material in its original order.

I trust this helps.

Brian Barker

#4

Only if numbers are as text, you can add it as sort list.
Select the range.
Menu/Tools/Options/LibreOffice calc/Sort List - Copy List from - Copy

To use the sort list, select the range to sort and use Menu/Data/Sort, on
the 'Options' tab, select the sort list in 'custom sort order'.

But sort list it's not exported with the file.

Miguel Ángel.

#5

Hi,
My understanding is you want to re-display the list in the order you provided.

Set B2 to B17 to your list
Then set A2 to A17 to your required order. Note in column A you are telling the numbers in column B where they should appear in the sorted list i.e Number 16 (sixteen) in column B should end up as number 2 in the sorted list.
Select A2 to A17 and do a sort.
When asked if you want to extend the sort click "Extend"

    A2 to A17 B2 to B17 before sort B2 to B17 after sort Your required order

Hope this makes sense

Tony Bray
tonybsa@mac.me
MacBook Pro 15 inch Mid 2009
2.8 GHz Intel Core 2 Duo, 4 GB RAM,
Mac OS X 10.11.2
LibreOffice 4.4.5
Scribus 1.4
Parallels Desktop 11.02
Ubuntu 14.04
LibreOffice 4.4.4