Conditional Formula Requested

Book2.xlsx

Hey all,

So i've tried a few different rules and can't seem to find a way to get the desired result, so i'll lay it out here as simple as I can to try to get the best answer/result possible.


I want a formula that I can enter a main item, and up to 4 sub items and it will tel me how to get those 4 sub items to be with the main item.

Example:

5 Main Items
20 Sub items
10 Conditions

Not all Main Items can just be combined with each sub item, but some main items can have certain sub items when certain conditions are met:

Main item 1 cannot have sub item 5 directly by itself, but Main item 2 can. So I need to find a way to get sub item 5 from main item 2 to main item 1.

In this particular case;
Main item 1 has condition 2 and 3 and is able to have sub item 1,2,3,4 directly, and all sub items indirectly
Main item 2 has condition 4 and 5 and is able to have sub item 5,6,7,8 directly, and all sub items indirectly
Main item 3 has condition 4 and 7 and is able to have sub item 9,10,11,12 directly, and all sub items indirectly
Main item 4 has condition 7 and 2 and is able to have sub item 13,14,15,16 directly, and all sub items indirectly
Main item 5 has condition 9 and 10 and is able to have sub item 17,18,19,20 directly, and all sub items indirectly


So with this in place it would appear I can use Main Item 2 with condition 4 to attach sub item 5 to main item 3, then use main item 3 with condition 7 to attach sub item 5 to main item 4, then with main item 4 using condition 2 i can atatch sub item 5 to main item 1.

Therefore receiving my desired result of Main Item 1 with Sub item 5.

Now, what i want is a formula that can work all of this out by displaying this info on hidden sheets and just having a few selection boxes on the main sheet to select the desired main item with the desired sub items and then display the required chain that is needed.

Now once i have that (if its possible), i'd like to goa little deeper and sub conditions, so Main Item 2 can directly have sub item 5, but only when Sub condition 50 is met. Now, sub condition 50 can always be met, but i just want it to display that int eh final tree:

Ie: Main Item 1 with Sub item 5 = Main Item 2 SC50 > Main Item 3 > Main Item 4 > Main Item 5

i'd also like it to display up to 4 possible sub items, if that many are chosen. So you may Select Main Item 1 with either 1,2,3 or 4 sub items attached.


Have been playing around with VLOOKUP and data ranges etc but I just can't figure out how to add in the conditions

The finished product will have hundreds of each main item, sub item, conditions etc.


Thanks all, I hope i've been clear enough.



EDIT: Also, if the Main Item cannot have a requested Sub Item, then to display a Not Available message or something similar.