Is there any way to automate this in my spreadsheet? [closed]

edit

I'm not the best person to answer this since I don't have much experience with Office stuff. So keep in mind: there might be better ways, e.g. a macro; hopefully someone will write answers on that.

But I've been lately twiddling with scripting LO Calc, and I figured I could share some of what I learned, and answer your question.

LibreOffice supports scirpting through UNO API. There're various language backends to it, here I'm using Python. You may need to install some python package for import uno line to work (e.g. on Fedora it's libreoffice-pyuno package).

Here's a code that does what you asked for:

#!python
import uno

I_COL_TO_READ_FROM = 0 # the column with numbers
I_COL_MARKS        = 1 # the column with "X"es
I_COL_TO_WRITE_TO  = 2 # the empty column to write new numbers to
MARK = 'X'

# run libreoffice as:
# soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"

def connectToLO():
    # get the uno component context from the PyUNO runtime
    localContext = uno.getComponentContext()
    resolver = localContext.ServiceManager.createInstanceWithContext(
        "com.sun.star.bridge.UnoUrlResolver", localContext )
    # connect to the running office
    ctx = resolver.resolve( "uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext" )
    smgr = ctx.ServiceManager
    desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
    return desktop.CurrentComponent

# the "unused rectangle" by default is 1048576×1024, which probably isn't something
# you might be interested in
def getUsedRectangle(sheet):
    cursor = sheet.createCursor()
    cursor.gotoEndOfUsedArea(False)
    cursor.gotoStartOfUsedArea(True)
    return cursor

# applies f to every row in the range
def foldRows(rectangle, f, accum):
    for row in rectangle.Rows:
        accum = f(row, accum)

def fillNewCol(row, col_write_to):
    (dst_col, row_index) = col_write_to
    if row.getCellByPosition(I_COL_MARKS, 0).String == MARK:
        dst_cell = dst_col.getCellByPosition(0, row_index)
        # see comments under the post: a cell has different state when a number is
        # assigned compared to a string. So here I test for whether we're dealing
        # with strings or numbers. But you may want to remove overhead of this test
        # if you know what you deal with right away
        src_cell = row.getCellByPosition(I_COL_TO_READ_FROM, 0)
        if src_cell.String.isdigit():
            dst_cell.Value  = src_cell.Value
        else:
            dst_cell.String = src_cell.String
        return (dst_col, row_index+1)
    return (dst_col, row_index)

focused_sheet = connectToLO().CurrentController.ActiveSheet
used_range = getUsedRectangle(focused_sheet)
foldRows(used_range,
        fillNewCol,
        (focused_sheet.Columns.getByIndex(I_COL_TO_WRITE_TO), 0))

The main part is implemented at fillNewCol: it checks rows for a mark, and writes to the new column as needed.

You may want to tweak column indices in I_COL_TO_READ_FROM, I_COL_MARKS, and I_COL_TO_WRITE_TO variables to accord to your spreadsheet. They're "hardcoded" for simplicity, though ideally maybe one could derive them from column names or whatever. And similar with MARK field.

Otherwise, the code is hopefully self-descriptive, but feel free to ask.

Here's how you can use it:

1. Run LibreOffice in server-mode as soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"
2. In appeared LO Calc window open the spreadsheet you wanted to work with (or just make something up for testing)
3. Save the python code as calc:gen-new-col.py file ...