2

I have two data sets:

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

first data set

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

second data set

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

output dataset

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

4
  • Welcome to Web Applications Stack Exchange. That looks doable with a lambda formula. Please edit the question to show the data and desired results using tables instead of screenshots. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Commented Dec 14, 2022 at 22:04
  • tnank you very much for your comment. I just created a table and added it to my sheet. Can you please specify how I can solve my problem with a lambda function? I googled it, but all I can find is, that it creates a custom function :-( do you have an idea what the lambda function would like in this case? Thank you very much in advance Commented Dec 14, 2022 at 22:50
  • Why do packages have the same name but different products?
    – Blindspots
    Commented Dec 15, 2022 at 0:55
  • because it's a serial dataset that displays every product that's in a certain product package. You can see it the other way round It's like a list of products that shows the package they're in. Commented Dec 15, 2022 at 7:19

1 Answer 1

0

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.

3
  • Hey there, I'm really greatful for your help, I tired the formular with data sets in different sheets. in the sample sheet it works (see "Test with multiple sheets") but when I transfer the formular in my original sheet this error comes up: "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows". I checked the formula a couple of times now and it's the same as in the sample sheet, just with different arrays - do you have an idea why this could be? Commented Dec 15, 2022 at 10:25
  • I inserted my formula in the example sheet and I inserted the sesult and the error. I be really greatful if you could take a look Commented Dec 15, 2022 at 10:30
  • In your German spreadsheet, you need to use different separators. Replace { "Client", "product package", "products" } with { "Client" \ "product package" \ "products" }. Commented Dec 15, 2022 at 13:34

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.