Sorting Question


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!

t82647.ods (13.1 KB)

How does this work with words and not actual numbers?

The numbers in my questions are arbitrary, the actual columns contain words

t82647_2.ods (13.8 KB)

1 Like

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:
    # 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.EndRow )
    for row in range(first, last, OFFSET+1):
        slicerange = _by_pos(_left, row, _right, min(row+OFFSET, last ))
1 Like

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 (Name a bit misleading. It’s a range cursor functionally.)
simpleSortRowByRow.ods (25.8 KB)

@Lupp: ***thisComponent.lockControllers() … thisComponent.unlockControllers() *** may help?