Forum Discussion

porridge's avatar
porridge
Copper Contributor
Jul 31, 2022
Solved

excel table

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 w...
excel
Macros and VBA
  • dscheikey's avatar
    dscheikey
    Jul 31, 2022

    Hiporridge, 

    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.

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Aug 01, 2022

porridge 

=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:

porridge's avatar
porridge
Copper Contributor
Aug 01, 2022
I just went through this file. As I am still learning EXCEL, I have to mention that I have never used the LET function. But thank you so much for using it and showing me these results. It's perfectly done, I understood the use of the IF function. Thank you so much kind sir for this work. I will try to work on this again, but with different parameters to master it. Thank you again!!!

Resources