I have a list of products (bundles of items, eg a number of different books) on single rows in a spreadsheet.


product book 1 book 2 book 3 book 4
Product A ISBN 1 ISBN 2 ISBN 3
Product B ISBN 11 ISBN 12 ISBN 13 ISBN 14

I'd like to split each row into separate rows like so:

product include
Product A ISBN 1
Product A ISBN 2
Product A ISBN 3
Product B ISBN 11
Product B ISBN 12
Product B ISBN 13
Product B ISBN 14

The products may have different numbers of books included, and I am pulling data from a spreadsheet with other columns (ie the info needed is not in consecutive columns).

Looking for an arrayformula (I think) that can achieve this, any pointers?


Managed a flat version of this using the following - BUT how can this be used to process lots of rows with arrayformula?

'products'!D1,'products'!D1,'products'!D1,'products'!D1,'products'!D1,'products'!D1, 'products'!D1; 

which looks like:

bundle_product_id bundle_variant_id child_product_id child_variant_id
product_1 variant_id ISBN 1 PRODUCT ID ISBN 1 VARIANT ID
product_1 variant_id ISBN 2 PRODUCT ID ISBN 2 VARIANT ID
product_1 variant_id ISBN 3 PRODUCT ID ISBN 3 VARIANT ID
product_1 variant_id ISBN 4 PRODUCT ID ISBN 4 VARIANT ID
product_1 variant_id ISBN 5 PRODUCT ID ISBN 5 VARIANT ID
product_1 variant_id ISBN 6 PRODUCT ID ISBN 6 VARIANT ID
product_1 variant_id ISBN 7 PRODUCT ID ISBN 7 VARIANT ID

1 Answer 1


This is the formula that I wrote, based on your first question:

=index(split(flatten("product|include",A2&"|"& Filter(B2:2,B2:2<>""),A3&"|"& Filter(B3:3,B3:3<>"")),"|"))

Essentially Flatten() appends the elements of a row, Filter () checks that the cells are not empty, Split() creates two columns of data. I think that it solves your problem.

update: filter() now verifies that the cells examined contain the codes of the books

See the test sheet

  • That's very helpful @Daniele, thanks. How would you combine this with arrayformula to go through dozens of rows?
    – Mr_P
    Commented May 26, 2022 at 6:34
  • That formula expands at this moment by itself, based on each new column of books, credit to index (). Automatic expansion ALSO based on the number of lines of the various products is currently not provided
    – Daniele
    Commented May 26, 2022 at 7:16
  • Although I don't like it very much (I couldn't simplify it), the second formula should answer your new request for extension to all lines
    – Daniele
    Commented May 26, 2022 at 10:47
  • Aha very interesting! Having a look now! Thanks @Daniele
    – Mr_P
    Commented May 26, 2022 at 11:28
  • I've been looking at this and scratching my head trying to extend it. I'm trying to add another column to this, with a product VARIANT (every ISBN also has a VARIANT)... I've started to set this up on your demo page, but can't see how to create the third column? Any hints greatly appreciated @Daniele !! 8•)
    – Mr_P
    Commented May 26, 2022 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.