1

Spreadsheet Example

I've made a spreadsheet that tracks what items I need to buy based on what recipies I want to make during the week. This is generated using a few different formulas and then displayed in column C.

I then have a list of ingredients (Item) that I can tick to say if I already have it in stock.

I need a formula which will check if any of the cells in column B are ticked, and if true remove the item in column A from the list in column C, and display the new refined list in column D.

I've tried UNIQUE, SORT, FILTER and QUERY and I can't seem to figure out which combination/format would work.

Is this possible? Thank you for any help!

Improve this question

1 Answer 1

Reset to default
1

Probably someone can do it better, I solved it with this formula:

=ifna(filter(C3:C,countif(filter(A3:A,B3:B),C3:C)=0))

Explanation (starting from the inner part of the formula)

filter(A3:A,B3:B) returns the items owned

countif(filter(A3:A,B3:B),C3:C)=0 returns TRUE for any item in column C that is unselected in B or missing in column A

filter(C3:C,countif(filter(A3:A,B3:B),C3:C)=0) finally returns the missing items

test sheet

Improve this answer
5
  • I'm sorry, I can't seem to get this one to work. If I select anything in column B it doesn't change anything in the final column for me? Could this be down to column C being a formula rather than just plain text?
    – Echo
    Commented Aug 24, 2022 at 5:57
  • I don't think so ... try to check it on the test sheet I attached to my answer
    – Daniele
    Commented Aug 24, 2022 at 6:31
  • Apologies, I missed that you'd attached a test sheet. For some reason, despite setting it up the exact same way (or at least I think I have) it still doesn't work. It just returns column C with no adjustments. I've made a copy onto your test sheet if you wouldn't mind taking a look and seeing if i've set it up incorrectly? Thank you for the help so far by the way! :)
    – Echo
    Commented Aug 24, 2022 at 7:25
  • it took me some time but I discovered the mystery ... your ingredient list (the one in column J) was not trimmed (it had spurious spaces in almost all elements) ... with their current correction everything works as it should.
    – Daniele
    Commented Aug 24, 2022 at 9:52
  • You're fantastic, thank you! I'm not sure what's causing the aditional spaces. The formula I have the is used prior to that full list being generated must be added a space somehow. I'll do some digging and see if I can figure out whats causing it to add spaces at the end. Thank you for this, never would've figured that one out! :D EDIT - By adding a simple TRIM to the other formula it's fixed it. Can't thank you enough!! :D
    – Echo
    Commented Aug 24, 2022 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.