Extracting data from multiple Google Sheets using REDUCE and FILTER functions

Other Questions Google Sheets
1

Hey everyone! I’m stuck on a tricky Google Sheets problem. I’ve got three spreadsheets:

  1. A product catalog with recipes
  2. A schedule with product iterations
  3. A components list

I need to compare the catalog and schedule, then fill the components list with specific product iterations. I’ve tried QUERY and ARRAYFORMULA, but no luck.

Here’s what I want to do:

  • Look up a component in the catalog
  • Find all products using that component
  • Search for those products in the schedule
  • Import all iterations into the components list

I’ve got over 400 components and iterations, so it’s a big job. Any ideas on how to tackle this? I’m thinking maybe REDUCE and FILTER could help, but I’m not sure how to combine them.

Thanks for any help you can give!

2

hey there! i’ve dealt with similar stuff before. have you tried using QUERY with multiple sheets? something like:

=QUERY({IMPORTRANGE(sheet1, “A:Z”); IMPORTRANGE(sheet2, “A:Z”)}, “select * where Col1 = ‘YourComponent’”)

this could pull data from both sheets into one query. then use FILTER to narrow it down. might be easier than REDUCE. just a thought!

3

I’ve tackled a similar challenge before, and I found that combining REDUCE and FILTER functions can indeed be quite effective for this type of task.

Start by using FILTER to narrow down your product catalog based on the component you’re looking for. Then, use REDUCE to iterate through the filtered results and look up corresponding entries in your schedule.

You can nest these functions to create a formula that pulls all the relevant data into your components list. Something like:

=REDUCE(, FILTER(catalog_range, component_column = lookup_value), LAMBDA(acc, row, FILTER(schedule_range, product_column = INDEX(row, product_index))))

This is a simplified version, of course. You’ll need to adjust the ranges and column references to match your specific sheets. Also, consider using QUERY for more complex filtering if needed.

Given the scale of your data, you might want to implement this in stages to avoid performance issues. Start with a small subset to test, then scale up gradually.

4

As someone who’s dealt with similar data extraction challenges, I can tell you that REDUCE and FILTER are definitely the way to go here. They’re powerful when combined correctly.

Here’s what I’d suggest based on my experience:

Start by using FILTER to get all products with a specific component from your catalog. Then, use REDUCE to iterate through these filtered results and match them against your schedule.

The trickiest part is getting the syntax right. It took me a while to figure it out, but once you do, it’s a game-changer. You’ll want something like:

=REDUCE(, FILTER(catalog, component_col = lookup_val), LAMBDA(acc, row, FILTER(schedule, product_col = INDEX(row, product_idx))))

This formula looks intimidating, but it’s actually quite efficient once you get it working. Just make sure to adjust the range references to match your sheets.

One word of caution - with 400+ components, you might hit some performance issues. I’d suggest testing with a smaller dataset first, then scaling up gradually. Good luck!