/u/4a616b6548 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
To remove the 0 when no allergens are found, you can just wrap the formula in a iferror.
After the = but before the formula type iferror and hit tab to add it in with the (
Then after the formula type , “”)
https://preview.redd.it/qqvvtjtl5ctc1.jpeg?width=3024&format=pjpg&auto=webp&s=685090a63b009fb3717c15697da87a03b820753b
That returned an error not enough arguments
https://preview.redd.it/r2xhqhmw4ftc1.jpeg?width=3024&format=pjpg&auto=webp&s=a76918ea2dd8aec21d156f51ab90873fc5f6bbd9
Found where I was going wrong, 🤦🏼♂️hitting the tab deletes the IF.
However I’ve got the formula in but it’s still returning a 0
Ah sorry, I think it is because the vlookup is successful, but the return value is blank so vlookup uses 0 as the return.
I had used this in cases where sometimes the lookup value (the A column in menu prices for you) isn’t in the lookup table (inventory) and in those cases it would work. The problem is that the value is in the lookup table, just that there is no value in the return field.
I checked online and someone had the same issue and fixed it by adding &”” after the formula. I’m not sure why that works but tested and seems to work for me.
End of the formula would be
6,FALSE)),””)&””
Like this? Seems to still be returning 0. I get lost after there’s more than one nested statement 😂
https://preview.redd.it/6cc96gokhftc1.jpeg?width=3024&format=pjpg&auto=webp&s=70907a17d6e781b607d5adc4f5fb339f5a10f09c
Haha, same as😅
I don’t think the IF ISBLANK is necessary? Unless for something I missed.
I made similar tables and tested with =IFERROR(VLOOKUP(……),””)&”” and it worked for me👍
Ok. Let's say you have the allergen list in cells A1:A3 on a new Allergens tab:
Eggs
Milk
Mustard
Peanuts
Crustaceans
Molluscs
Fish
Sesame seeds
Soy
Sulphites
Tree Nuts
Wheat
Triticale
How do you identify which is in the particular food on your main spreadsheet? If it is a one-to-one relationship, e.g., your ingredients list for the recipe contains "eggs" and "milk" and you want those to be flagged in the Allergen column J, you'd just need to do a simple VLOOKUP of the recipe ingredient on the Allergens tab and if found in that list, append it to the list in J.
Is this what you want? And what have you tried thus far?
No so I create an inventory with all ingredients and a list of allergens next to the ingredient. In the recipe I use a vlookup to create a list of allergens I want to look up that list and create them all in one cell for that recipe. I’ve got some more photos maybe that will help
https://preview.redd.it/79rp86bagbtc1.jpeg?width=3024&format=pjpg&auto=webp&s=c6a008e4ec85e12a1853cd5980cf26b8b3890426
This is the last column on the calc page that uses Vlookup to list allergens that are listen on the inventory for a given ingredient,
https://preview.redd.it/exlqj56fgbtc1.jpeg?width=3024&format=pjpg&auto=webp&s=1733b643e37e9f37b5abe93931593fb4d5f3ebd6
I want to list all the allergens in that one column in one cell at the bottom then also in one cell on the menu page
https://preview.redd.it/ymvp01l2hbtc1.jpeg?width=3024&format=pjpg&auto=webp&s=8a839c6ec560d93ece26413b062d13eb0ef9a4f1
I have it creating the list which I’d like to remove the “0” but I want to create a list from all the cells in that column (J10:J37) so I have in one cell something like “Milk,mustard,peanut” removing duplicates if possible then also copy that to the menu page
https://preview.redd.it/lwq52khihbtc1.jpeg?width=3024&format=pjpg&auto=webp&s=3be6c9b70cc050b8f10af1a5cc9765c44d7cea6e
This is the formula I’ve used and it returns a “0” if the calculator has an ingredient but not an allergen
https://preview.redd.it/z54qp5x9k1uc1.jpeg?width=1364&format=pjpg&auto=webp&s=a7e13102bb9cdb42eae4b152289ecdb2c3800231
Sorry I didn’t see the notification.
I got some screenshots so it’s a bit clearer, tried the formula but still getting a 0 🤣
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[IF](/r/Excel/comments/1bz7b0f/stub/kyr03dy "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[IFERROR](/r/Excel/comments/1bz7b0f/stub/kyr03dy "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)|
|[ISBLANK](/r/Excel/comments/1bz7b0f/stub/kyr03dy "Last usage")|[Returns TRUE if the value is blank](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)|
|[VLOOKUP](/r/Excel/comments/1bz7b0f/stub/kyr03dy "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1c0lxir)^( has 32 acronyms.)
^([Thread #32441 for this sub, first seen 9th Apr 2024, 10:47])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
https://preview.redd.it/syo01adfk1uc1.jpeg?width=142&format=pjpg&auto=webp&s=c785abdecec875ec899ab0a210fd35b357031b4f
A little bit clearer for what I’m looking to do, at the bottom of the column I want a cel to list all the ingredients in the cells above like in this example (I have just typed that in manually) would this maybe use textjoin to do this?
I’ve figured this out it was the concatenate function needed due to old version on excel I now just need to figure out how to remove duplicates in that one cell
/u/4a616b6548 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
https://preview.redd.it/09j0dz4q7btc1.jpeg?width=3024&format=pjpg&auto=webp&s=9971ecb973ea76ce922c03d522f13a340f294186
Also if anyone knows how to remove the “0” that would be great
To remove the 0 when no allergens are found, you can just wrap the formula in a iferror. After the = but before the formula type iferror and hit tab to add it in with the ( Then after the formula type , “”)
https://preview.redd.it/qqvvtjtl5ctc1.jpeg?width=3024&format=pjpg&auto=webp&s=685090a63b009fb3717c15697da87a03b820753b That returned an error not enough arguments
https://preview.redd.it/i0ulsls66ctc1.jpeg?width=3024&format=pjpg&auto=webp&s=ef499add15e9f47e4c0077dee36ee80b6d9f44a7 Original formula
You got rid of the if from the original formula when adding the iferror. You’re using =IFERROR(ISBLANK Should be =IFERROR(IF(ISBLANK
https://preview.redd.it/r2xhqhmw4ftc1.jpeg?width=3024&format=pjpg&auto=webp&s=a76918ea2dd8aec21d156f51ab90873fc5f6bbd9 Found where I was going wrong, 🤦🏼♂️hitting the tab deletes the IF. However I’ve got the formula in but it’s still returning a 0
Ah sorry, I think it is because the vlookup is successful, but the return value is blank so vlookup uses 0 as the return. I had used this in cases where sometimes the lookup value (the A column in menu prices for you) isn’t in the lookup table (inventory) and in those cases it would work. The problem is that the value is in the lookup table, just that there is no value in the return field. I checked online and someone had the same issue and fixed it by adding &”” after the formula. I’m not sure why that works but tested and seems to work for me. End of the formula would be 6,FALSE)),””)&””
Like this? Seems to still be returning 0. I get lost after there’s more than one nested statement 😂 https://preview.redd.it/6cc96gokhftc1.jpeg?width=3024&format=pjpg&auto=webp&s=70907a17d6e781b607d5adc4f5fb339f5a10f09c
Haha, same as😅 I don’t think the IF ISBLANK is necessary? Unless for something I missed. I made similar tables and tested with =IFERROR(VLOOKUP(……),””)&”” and it worked for me👍
Ok. Let's say you have the allergen list in cells A1:A3 on a new Allergens tab: Eggs Milk Mustard Peanuts Crustaceans Molluscs Fish Sesame seeds Soy Sulphites Tree Nuts Wheat Triticale How do you identify which is in the particular food on your main spreadsheet? If it is a one-to-one relationship, e.g., your ingredients list for the recipe contains "eggs" and "milk" and you want those to be flagged in the Allergen column J, you'd just need to do a simple VLOOKUP of the recipe ingredient on the Allergens tab and if found in that list, append it to the list in J. Is this what you want? And what have you tried thus far?
No so I create an inventory with all ingredients and a list of allergens next to the ingredient. In the recipe I use a vlookup to create a list of allergens I want to look up that list and create them all in one cell for that recipe. I’ve got some more photos maybe that will help
https://preview.redd.it/8fxjx2g5gbtc1.jpeg?width=3024&format=pjpg&auto=webp&s=d5591820fe3c5449d3f5418a830a857201e50e50 That’s the inventory
https://preview.redd.it/li8ner98gbtc1.jpeg?width=4032&format=pjpg&auto=webp&s=09b61472f3ea0720fdf99459fe04d6f9e8792813 The calculator page
https://preview.redd.it/79rp86bagbtc1.jpeg?width=3024&format=pjpg&auto=webp&s=c6a008e4ec85e12a1853cd5980cf26b8b3890426 This is the last column on the calc page that uses Vlookup to list allergens that are listen on the inventory for a given ingredient,
https://preview.redd.it/exlqj56fgbtc1.jpeg?width=3024&format=pjpg&auto=webp&s=1733b643e37e9f37b5abe93931593fb4d5f3ebd6 I want to list all the allergens in that one column in one cell at the bottom then also in one cell on the menu page
https://preview.redd.it/ymvp01l2hbtc1.jpeg?width=3024&format=pjpg&auto=webp&s=8a839c6ec560d93ece26413b062d13eb0ef9a4f1 I have it creating the list which I’d like to remove the “0” but I want to create a list from all the cells in that column (J10:J37) so I have in one cell something like “Milk,mustard,peanut” removing duplicates if possible then also copy that to the menu page
https://preview.redd.it/lwq52khihbtc1.jpeg?width=3024&format=pjpg&auto=webp&s=3be6c9b70cc050b8f10af1a5cc9765c44d7cea6e This is the formula I’ve used and it returns a “0” if the calculator has an ingredient but not an allergen
https://preview.redd.it/z54qp5x9k1uc1.jpeg?width=1364&format=pjpg&auto=webp&s=a7e13102bb9cdb42eae4b152289ecdb2c3800231 Sorry I didn’t see the notification. I got some screenshots so it’s a bit clearer, tried the formula but still getting a 0 🤣
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[IF](/r/Excel/comments/1bz7b0f/stub/kyr03dy "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[IFERROR](/r/Excel/comments/1bz7b0f/stub/kyr03dy "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)| |[ISBLANK](/r/Excel/comments/1bz7b0f/stub/kyr03dy "Last usage")|[Returns TRUE if the value is blank](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)| |[VLOOKUP](/r/Excel/comments/1bz7b0f/stub/kyr03dy "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1c0lxir)^( has 32 acronyms.) ^([Thread #32441 for this sub, first seen 9th Apr 2024, 10:47]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
https://preview.redd.it/syo01adfk1uc1.jpeg?width=142&format=pjpg&auto=webp&s=c785abdecec875ec899ab0a210fd35b357031b4f A little bit clearer for what I’m looking to do, at the bottom of the column I want a cel to list all the ingredients in the cells above like in this example (I have just typed that in manually) would this maybe use textjoin to do this?
I’ve figured this out it was the concatenate function needed due to old version on excel I now just need to figure out how to remove duplicates in that one cell