Excel Data Manipulation - parse, match and create
I've not seen or used petl before, but there are some ways to improve your code.


Move things out of the global scope.
Make a function for your most common functions.
Change petl.select to petl.selectis or petl.selectisnot.


Since you do:


nok_rows = petl.antijoin(serial_rows, petl.cut(stacked_joins, ['serial number']), key='serial number')
nok_rows = petl.addfield(nok_rows, 'file', 'NOK')



So many times, you should change it to a function.
This function could take a fn of petl.antijoin, a left of serial_rows, a right of stacked_joins and a field of 'NOK'.

And so you can use:

def change_row_file(fn, left, right, field):
    rows = fn(left, petl.cut(right, 'serial number'), key='serial number')
    return petl.addfield(rows, 'file', field)

change_row_file(petl.antijoin, serial_rows, stacked_joins, 'NOK')


Adding this, with the changes to petl.select, to main_compare can result in something like:

def main_compare(table):
    serial_rows = petl.selectisnot(table, 'serial number', None)
    return petl.stack(
        change_row_file(petl.join, serial_rows, main, 'OK'),
        change_row_file(petl.antijoin, serial_rows, main, 'NOK'),
        petl.selectis(table, 'serial number', None))


There's not much more that I think could do with improving apart from having nothing in your global scope.
And then adding a global constant for the serial column, say SERIAL_COLUMN = 'serial number'.
This significantly reduces the amount of magic strings, to ones that aren't repeated (except 'NOK').

This can result in (Not pep8 compliant to remove a scroll bar):

import petl
SERIAL_COLUMN = 'serial number'

def change_row_file(fn, left, right, field):
    rows = fn(left, petl.cut(right, SERIAL_COLUMN), key=SERIAL_COLUMN)
    return petl.addfield(rows, 'file', field)

def update_main(main, one, two):
    serial_rows = petl.selectisnot(main, SERIAL_COLUMN, None)
    stacked_joins = petl.stack(
        change_row_file(petl.join, serial_rows, one, 'ok, 1.xlsx'),
        change_row_file(petl.join, serial_rows, two, 'ok, 2.xlsx'))
    return petl.stack(
        stacked_joins,
        petl.selectis(main, SERIAL_COLUMN, None),
        change_row_file(petl.antijoin, serial_rows, stacked_joins, 'NOK'))

def main_compare(main, table):
    serial_rows = petl.selectisnot(table, SERIAL_COLUMN, None)
    return petl.stack(
        change_row_file(petl.join, serial_rows, main, 'OK'),
        change_row_file(petl.antijoin, serial_rows, main, 'NOK'),
        petl.selectis(table, SERIAL_COLUMN, None))

def main():
    main = petl.fromxlsx('main.xlsx')
    one = petl.fromxlsx('1.xlsx', row_offset=1)
    two = petl.fromxlsx('2.xlsx')

    petl.toxlsx(update_main(main, one, two), 'mainNew.xlsx')
    petl.toxlsx(main_compare(main, one), '1New.xlsx')
    petl.toxlsx(main_compare(main, two), '2New.xlsx')

if __name__ == '__main__':
    main()