Input combinations for single output

Hello all,

I am hoping the Excel wizards of this forum can help me make something to make my life (and that of my colleagues!) mush easier.

I have had an idea about an Excel 'one-stop-shop' file that seems unfortunately beyond my skill-set and knowledge to create.

We currently work with several hundred 'categories' that we may assign to different people in a day. Each of these categories contain dozens of 'assignments'. We will often need to create combine several sets of categories to achieve a very specific mix, I will call this combination 'profiles'. Once this 'profile' is created, we need to follow a specific naming convention. There are also further variables such as should this 'profile' be focused on fruit only/veg only/both fruit and veg. And should it include only assignments with 'apple' in the name, or assignments with 'apple' and 'orange' etc... (there would be three fruits to choose from).
(I really hope that made sense! Please let me know if clarification is needed!)

So my idea is that we could use a file, and on one tab we have a list of all existing categories and their relevant assignments(is each assigment a fruit or veg in one columne, what type of fruit in another etc). On a different tab in the file I would like a row with several cells containing 'drop-down' menus that must be selected. And once all the drop-down's are selected, the file will return an output list of all assignments necessary, and in the order all these assignments should be listed in the new 'profile'.

An example of a possible output - I need to create a new 'profile' to match a nutritional need.

I open the file and select that I would like this nutritional profile to contain both 'fruit' and 'veg' and 'dairy'. Since fruit and veg will always be a higher priority than dairy, the output returns a list of assignments that rank then as such. Within these rankings, it also recognises that I would prefer to have organic food before non-organic food, so the list begins to look like this

1. Organic Broccoli
1. Organic Apple
2. Non-organic Broccoli
2. Non-organic Apple
3. Organic Dairy
4. Non-organic Dairy

And finally, based on the combination of categories I have in this 'profile', it should return a specific naming convention like: KEVYWEVZ_ORG_VEG_FRUIT_DAIRY

I realise this ask may not be particularly well explained but any assistance you can offer (even if it is just pointing me in the direction of what functions/tools to explore) would be massively appreciated.

Kindest regards,
KW

Apologies Ali, I do not mean to have this project completed for me at all, but I am simply so stuck on where to even start in achieving this I wanted to give people the full context of what I was trying to achieve

I hope the attachment has worked

Bump for visibility

Apologies Ali, this is a busy forum and I do not have an expected response time but was just trying to ensure the post was not lost amongst all the queries. I am of course happy to abide by forum rules/etiquette

I am struggling to outline the need without divulging any sensitive data

Will do my best here

On the sheet I provided, row 16 of the 'Input' tab has cells highlighted in yellow, each with a dropdown menu. In this these dropdowns are a few options. When an option is selected for each of these yellow cells, that is the 'combination' I am looking for.

I would then like the Output tab to return the assignments according to the combination I selected in the Input tab

The key point is that some categories will always be a higher priority than others (the example I gave in the sheet was that organic produce will always be a higher priority than non-organic produce)

The data tab is included so that the categories outlined in the 'Input' tab can be matched with the correct assignment/fruit. In the actual data sets I will be using this data tab will be thousands of rows

Hopefully this might clear it up just a little bit. If it does I will edit my first and second post to this so as to make keep people's attention! :D

Thanks for your efforts here Ali, they truly are very much appreciated!

1.) A foodstuff can be categorised as a desert or a snack (example, cheese is a desert, but yoghurt is a snack), but it cannot be both. Both a desert and a snack can be requested in the combination but any one food type(/assignment) cannot be both.
2.) The cells highlighted in orange on the OUTPUT tab is what I hope would be the result of the combinations selected in the INPUT tab. X options selected in the INPUT tab result in Y combination in the OUTPUT tab

Originally Posted by AliGW

So what is the rationale for it changing from SNACK in the drop-down selector to DESSERT (see how I spelt that?) in the code?

Point taken!

Originally Posted by AliGW

How have you decided on that particular selection?

The order is as below

Priority Assignment
1 Org-Apple
1 Org-Orange
1 Org-Banana
1 Org-Kiwi
1 Org-Blueberry
2 Org-Yoghurt
3 Apple
3 Orange
3 Banana
3 Kiwi
3 Blueberry
4 Yoghurt

Here, anything that is organic (with identifier in the DATA tab and selected in the INPUT tab) will take priority over anything non-organic. Concurrently, the fruit family will always be a higher priority than the dairy family. Hence the above order

(hoping it format correctly in this post)

So there will always be a hierarchy or priority for all categories.

In this list, Fruit/Veg has a quicker 'Use Before' date than Dairy(for argument's sake). So if Fruit and/or Veg is selected then it must always be consumed first, hence it's higher priority than Dairy produce. Further still, Organic produce has an even shorter shelf-life than 'regular' produce so must be consumed even quicker.

So if the selection includes fruit and dairy with organic produce;

- Organic fruit produce will be highest priority
- Next priority will be 'regular' fruit produce
- Next will be organic dairy produce (as this is shorter shelf life than regular dairy, but still better than regular fruit)
- Lastly would be regular dairy

The data in orange is the expected output of the selections of the yellow cells in the INPUT tab