Hi,
I have a sorting questions. I have a sheet with multiple colums, these columns contain names of institutions, but they are not ranked into the correct columns, so they are all over the place. Here an abstracted example
A | B | C | D | E
1 | 2 | 5 | 4 | 3
3 | 4 | 2 | 1 | 5
2 | 5 | 2 | 3 | 1
How can i sort this in such a way that all the 1’s are in columns A, all 2’s are in columb B and so forth?
Thanks so much for the answer!
How does this work with words and not actual numbers?
The numbers in my questions are arbitrary, the actual columns contain words
may you describe what should happen with the word “2” wich occurs twice in the row??
Assuming the question is basically: “sort each row individually?” and also: “no need for keeping Formatting?” →→
# off topic:
# some grazy Variations of sort_key:
# key=lambda x: sum(map(ord, f"{x}"))
# key=lambda x: len(f"{x}")
def sort_rows( _range, reverse=False, key=lambda x: f"{x}"):
data = _range.DataArray
_range.DataArray = [sorted(row, reverse=reverse, key=key) for row in data]
def main_sort_row_by_row(*_):
doc = XSCRIPTCONTEXT.getDocument()
selection = doc.CurrentSelection
if selection.Rows.Count < 3000:
sort_rows(selection)
return
# below th switch to big-data-fans ;-)
# works so far with #300000 rows * 26 columns
OFFSET = 2500
sheet = selection.Spreadsheet
_by_pos = sheet.getCellRangeByPosition
_address = selection.RangeAddress
_left, _right, first, last = (_address.StartColumn,
_address.EndColumn,
_address.StartRow,
_address.EndRow )
for row in range(first, last, OFFSET+1):
slicerange = _by_pos(_left, row, _right, min(row+OFFSET, last ))
print(slicerange.AbsoluteName)
sort_rows(slicerange)
Basically this is one of the cases where standard tools (also functions) of spreadsheets work with ranges not offering an option to apply the tool per each single row or per each single column.
I would assume there isn’t a general (generalizeable) solution. However, it isn’t exactly difficult to get remedy based on user code - if urgently needed.
The attached example denonstrates a solution for the given case. most of the code is specific to that case. More general only is the loop processing the single rows (restricted to used columns) with the help of a com.sun.star.sheet.SheetCellCursor. (Name a bit misleading. It’s a range cursor functionally.)
simpleSortRowByRow.ods (25.8 KB)