Jul 31 2022 07:47 AM
Hello everyone. I am new here. I am currently learning excel and improving my skills.
I am currently working on a small college project, and I searched the internet but didn't get the answer I wanted. I tried many things but the end result wasn't successful.
I would like to mention the problem, and I would appreciate it if you could help me/ point me in the right direction.
I am working on a food project. There are many recipes (almost 70). My table consists of the SKU product code, recipe description, and the allergens present in them (3 allergens). I have made the table like this..vertically and horizontally in which there is the SKU and description and now in the middle, I want to have only those allergens that aren't common with the other recipe if I am comparing 2 recipes.
ex: if recipe 1 has 3 allergens (milk, mustard, and eggs) and the 2nd recipe has only eggs in it, so when I am doing a change of recipes in cooking, I want the cells to show the only allergens which aren't common. ie milk and mustard.
ps: I tried vlookup, if, ifs, but I wasn't able to solve it.
I believe that I came to the right place to find the solution, I look forward to your responses :)
Jul 31 2022 08:41 AM
Unfortunately, that image doesn't provide much help in understanding how you array your information in that table. So it's hard to give much in the way of the "direction pointing" that you're seeking.
One suggestion: look into the new FILTER function. It can do quite a few things that VLOOKUP can't.
If you can't resolve your situation with that, could I suggest you come back and post either the actual spreadsheet you gave there (or a mockup without revealing any actual recipes, but enough to appreciate how you are organizing the info on those allergens...... if you can't post the XLSX file here, post it on OneDrive or some other cloud service, and post a link to it here.
Jul 31 2022 09:10 AM
In this 1st pic, I have the recipes down and sideways, then in the middle, I am trying to find the answer to that allergen that isn't common with the other recipe. For example, recipe 1 has milk and eggs only no mustard, so when I am cooking my next recipe 2 which consists of mustard and eggs, the answer I should get in the middle cell should be milk only (because milk is not part of recipe 2) (But if I am going from recipe 2 to recipe 1, then the answer should change to the allergen mustard, as there's mustard in 2nd and not in 1st recipe)
The 2nd pic is the data from which I have to get the answer. (only the 1st table in the pic should be considered)
I hope I was able to explain the situation clearly. :))
till then I would try the FILTER FUNCTION.
Jul 31 2022 09:50 AM
Hi @porridge,
I have tried to understand your problem. I also approached the matter with FILTER(). But I also need an auxiliary column (column "R") Please do not delete it. If you move it, the formulas have to be adjusted with INDIRECT(). Please have a look at it and see if it suits your needs.
Jul 31 2022 10:10 AM
@dscheikey https://docs.google.com/spreadsheets/d/1PVcqYU_Zq23ARjiNtALBSLGqOdA-nvY5/edit?usp=sharing&ouid=11516...
The file you shared it is good when I am going from recipe 1 to 2 to 3 to 4 and so on.
I have attached the XLSX link to my file.
In my previous reply, I mentioned that when I am going from 1 to 2 the answer will be xyz, and then when am going back to 2 to 1, the answer will be different as the allergens present in both the recipes are different. And also there are recipes where they have the same allergens so the answer ends up being NA due to the same allergens.
you can go through my xlsx file to understand the data available. i have written the answers for the 1st 5 recipes manually so it gives an idea. the 2nd sheet in the file is the reference file. :))
Jul 31 2022 12:35 PM - edited Jul 31 2022 12:36 PM
SolutionHi@porridge,
I thought you wanted to do the assignment themselves as a school project. How many points will I get from the teacher? :)
I have now added new formulas to the file. The result does not match the manual specifications, as there were already errors in here.
I also tidied up so that no connected cells were left in the table.
Have a look at it and see if it corresponds to your ideas.
Jul 31 2022 02:44 PM
OMG!!. you didn't have to solve it entirely... I am speechless at what amazing work you have done. I was trying to figure out this for so many days. The file looked so perfectly well done. I went through it and it gives me the perfect idea of how to carry out the work.
Dear Excel wizard, can you please guide me on how to achieve this level of excel!!! I am still a novice. going through the trial and error method :)))
Thank you so much for your help. And yes you get a full grade for this assignment. without a doubt!!!
Aug 01 2022 11:34 AM
=LET(recipe,TRANSPOSE(FILTER(sheet3!$E$5:$G$42,sheet3!$C$5:$C$42=sheet2!D$4)),ingredient,TRANSPOSE(FILTER(sheet3!$E$5:$G$42,sheet3!$C$5:$C$42=sheet2!$B6)),IFERROR(TEXTJOIN(", ",,FILTER(ingredient,(recipe="")*(ingredient<>""))),"NA"))
If anyone wants to pull the ingredients (milk, mustard, eggs) which are in the side dish but not in the main dish this formula maybe returns the expected result. In order to simplify the solution i adapted the table containing the allergens to this (This is done easily with IF function):
The result would be like this with the main dish in horizontal order in rows 4 and 5 and the side dish in columns B and C:
Aug 01 2022 02:51 PM
Jul 31 2022 12:35 PM - edited Jul 31 2022 12:36 PM
SolutionHi@porridge,
I thought you wanted to do the assignment themselves as a school project. How many points will I get from the teacher? :)
I have now added new formulas to the file. The result does not match the manual specifications, as there were already errors in here.
I also tidied up so that no connected cells were left in the table.
Have a look at it and see if it corresponds to your ideas.