Hello I found this forum page Subtraction of List? which appeared to be exactly what I was looking for and was the solution for the topic creator. However the formula is not returning the results I was expecting and my data is a little bit more unique so Ive made a new post here.
So here is my set up:
Table 1 = a list of planets by type, Gas, Ice, Temperate etc. with lookup columns selecting an individual harvestable resource from Table 2, there is 5 columns of this type in this table.
Table 2 = a list of harvestable resources with one column that looks up all the planets that the resource can be harvested from.
Table 3 = has two lookup columns with one resource in each and a third column showing what the 2 combined resources can make. GOAL: I want a fourth column that list any planets where both resources can be harvested from. I think it is very similar to the .unique function, where it removes any duplicates I actually want to do the opposite and find the duplicates and list those instead if any exists.
To test out formulas easier I have made several other columns in Table 3 which are shown in the screenshots. My line of thinking is that if I combine the list of planets from each resource into one big list which will potentially contain duplicates and then subtract that list from the same one with .unique at the end then I should be left with the results im seeking.
As the other forum topic put it, it would be like this: (item 1, item 2, item 3) - (item 1, item 3) = item 2
or in my case (plasma, barren, oceanic, barren, temperate) - (plasma, barren, oceanic, temperate) = barren.
Essentially I have two Resources and their associated planets:
Resource 1 = plasma, barren
Resource 2 = oceanic, barren, temperate
In the above example I want the answer barren. Because barren is true in both list.
The formulas and techniques I have tried have not worked so hopefully someone can shed some light on this! Thanks
