Forum Discussion

crankcaller's avatar
crankcaller
Copper Contributor
Aug 16, 2023

Duplicate Row if any data in particular cell

  Hello, I have a spreadsheet of books. Roughly 3000 rows. Columns A to E are ISBN, Author etc. Columns F to N are Barcodes. Not all cells have barcodes.   If the title has one copy then there ...
excel
office 365
Patrick2788's avatar
Patrick2788
Silver Contributor
Aug 17, 2023

crankcaller

 

If you're looking for a formula (or a custom function), this will do it:

 

'ReShape Lambda
=LAMBDA(range,LET(
    header, HSTACK(TAKE(range, 1, 5), "Barcode"),
    data, DROP(range, 1),
    record, TAKE(data, , 5),
    barcodes, TAKE(data, , -9),
    bc_vector, TOCOL(barcodes, 1),
    StackBooks, LAMBDA(a, v,
        LET(
            c, SUM(MMULT((barcodes = v) * 1, SEQUENCE(9))),
            title, XLOOKUP(v, CHOOSECOLS(barcodes, c), record, ""),
            VSTACK(a, HSTACK(title, v))
        )
    ),
    REDUCE(header, bc_vector, StackBooks)
))

 

Resources