Display account numbers where stock items are not present.

[guerillaexcel]

Mind has drawn a blank so here I am.

Apologies for the description, don't really know how else to describe what Im looking for.

I have a list of account numbers with stock attached to each account. I want to show all account numbers that do not have any one of 5 items attached to it.
See my attached (simplified) example, show all accounts that dont have Bananas or Oranges, account number 222 and 444 would be displayed.

any ideas?

[amy_d2]

Hi

i think Pivot table will help you

[guerillaexcel]

Hi Amy, thanks for your reply. I have had a look but can't see how to do it with a pivot. Any thoughts?

[newdoverman]

Enter the stock items that you do have in a list. In this example H2:H5 (Mangos, Oranges, Bananas, Apples)

In C2 enter this formula and copy down the length of your data:

Please Login or Register  to view this content.

This will enter a 1 beside every account that doesn't contain an item in your stock list. You can now sort all the items with 1 in column C together if you so desire.

[guerillaexcel]

Thanks for your help!

[newdoverman]

You're welcome.