Quote Originally Posted by mather7 View Post
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.


1 Available Products 5 Available Accounts
2 Apple 1
3 Orange 2
4 Tomato 3
5 Spinach
6 Garlic

Spreadsheet Formulas
D1=COUNTA(ID[Available Products])

Excel tables to the web >> Excel Jeanie HTML 4

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


1 ProductAccount
2 Apple1
3 Orange1
4 Tomato1
5 Orange2
6 Spinach2
7 Garlic2

Excel tables to the web >> Excel Jeanie HTML 4

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.


1 AccountProduct Missing
2 1Spinach
3 1Garlic
4 2Apple
5 2Tomato
6 3Apple
7 3Orange
8 3Tomato
9 3Spinach
10 3Garlic

Spreadsheet Formulas
B2=IFERROR(INDEX(ACCOUNTS[Available Accounts],AGGREGATE(15,6,(ROW(ACCOUNTS[Available Accounts])-ROW(ACCOUNTS[#Headers]))/((COUNTIF(ACT[Account],ACCOUNTS[Available Accounts])+COUNTIF(B$1:B1,ACCOUNTS[Available Accounts])),1)),"")
C2=IF([@Account]="","",INDEX(ID[Available Products],AGGREGATE(15,6,(ROW(ID[Available Products])-ROW(ID[#Headers]))/(COUNTIFS(ACT[Account],[@Account],ACT[Product],ID[Available Products])=0),COUNTIF(INDEX([Account],1):[@Account],[@Account]))))

Excel tables to the web >> Excel Jeanie HTML 4