CODEWITHSUNDEEP
filtergoogle-sheets
dakaasin
dakaasin

Reputation: 5

Filter Data for Each Row in a Column

EVE Online Manufacturing Spreadsheet

In Batch!F3:G, I'm attempting to break down the data input from columns B3:C to their components (and eventually materials/minerals in I3:J) by using filter to compare results in Engine!P:R. Multiplied of course by the total number of each finished product I need.

I've been trying to figure out ways to arrayformula this together, and even tried quite a few query functions without success. The best I've been able to come up with is to string the actual formula together, appending them with {}, but this gets bloated quickly. I need this to be open ended because I have a tendency to build a lot of things at once. Any help would be appreciated, even just point me in the right direction!

Upvotes: 0

Views: 98

Answers (1)

HaPhan
HaPhan

Reputation: 321

Well, based on my limited knowledge about google sheet, I can only think of one way to do this automatically.

Here's a sheet I constructed based on your sheet. https://docs.google.com/spreadsheets/d/1AfX8o05gUGPiN5S90w4o0yxuIYjsJRaXsaYUFTJuEPo/edit?usp=sharing

First, on Engine sheet, add one more column which will give you the number of materials required for that part, which is looked up in the PART LIST of BATCH sheet. For this I use VLOOKUP, as you see in D2.

Then on BATCH sheet, query the materials that VLOOKUP return positive, multiply it by the amount of item and then sum them. This is done by the QUERY used in F3

This method only if you don't have duplicate item in your PART LIST, due to the way VLOOKUP work.

Of course if you want to break the material list further, you can do the same approach..

Upvotes: 1

Related Questions