automatically generate new dataset from two data sheets // merge google sheets

Question

I have two data sets:

first data set: list of clients who bought a sertain product package:

second data set: list of products for every product package:

what I need is to generate (automatically) a new dataset where an entry is created for every product that every client has bought:

This is only an example, since the to datasets are huge, copy past is not a solution. Is there even a way to do this in google sheets (appscript?) if not, do you know if this is possible in excel? Thank you very much in advance

here's the editable datasheet, where you'll find the example data https://docs.google.com/spreadsheets/d/1qtxW41ZiA2GbVOrKlD_G5M9xo8hC1GbkIBuAn5wbTnQ/edit#gid=2100307022

Answer 1

Use reduce(), filter() and match(), like this:

=lambda( 
  clients, packages, productsAndPackages, packageLookup, 
  reduce( 
    { "Client", "product package", "products" }, unique(filter(clients, len(clients))), 
    lambda( 
      result, client, 
      { 
        result; 
        filter( 
          { iferror(packageLookup / 0, client), productsAndPackages }, 
          match(packageLookup, filter(packages, clients = client), 0) 
        ) 
      } 
    ) 
  ) 
)( 
  A3:A10, B3:B10, E3:F12, E3:E12 
)

See your sample spreadsheet.