Find Missing Values

[mather7]

What i am trying to do is we have multiple accounts and products, would like to find the missing products for each account. I saw solutions for matching one set but not multiples

Account Product
1 Apple
1 Orange
1 Tomato
2 Orange
2 Spinach
2 Garlic

All Avail products
Apple
Orange
Tomato
Spinach
Garlic

Would like to see results of the difference between products and all available products

Account Product Missing
1 spinach
1 garlic
2 apple
2 tomato

and so on for other accounts.

 

[Peter_SSs]

Id try VBA,

I'm wondering if that means VBA is not your first preference? If that is the case, here is another possible formula approach but using the fact that you have tables.
Like stunnrock though, I am also assuming that there is, or could be, a list of all available Account Numbers as I'm thinking that it might be possible that no products at all are listed in the ACT table for 1 or more accounts.
Anyway, see if this is any use.

Here is my sheet with the table of available products (table ID) in column B, table of available account numbers (table ACCOUNTS) in column F & a helper cell in D1 to count the available products.

Excel Workbook
	A	B	C	D	E	F	G
1		Available Products		5		Available Accounts
2		Apple				1
3		Orange				2
4		Tomato				3
5		Spinach
6		Garlic
ID

This sheet contains the table of used accounts and products (table ACT)

Excel Workbook
	A	B	C	D
1		Product	Account
2		Apple	1
3		Orange	1
4		Tomato	1
5		Orange	2
6		Spinach	2
7		Garlic	2
ACT

Finally the results sheet with table RTN
The formulas shown should be copied down as far as you might ever need.
Note that this table has an example demonstrating my earlier point about an account (3) that does not appear at all in table ACT.

Excel Workbook
	A	B	C	D
1		Account	Product Missing
2		1	Spinach
3		1	Garlic
4		2	Apple
5		2	Tomato
6		3	Apple
7		3	Orange
8		3	Tomato
9		3	Spinach
10		3	Garlic
11
12
RTN