Forum Discussion
crankcaller
Aug 16, 2023Copper Contributor
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 ...
Patrick2788
Aug 17, 2023Silver Contributor
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)
))