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.

 

[My Aswer Is This]

We need specific details.
Like where is all this data?

Where is Account Products?
Where is Avail Products?
And where is

Account Product Missing

And where are these other accounts.

And are you willing to use Vba
​

 

[mather7]

We need specific details.
Like where is all this data?

Where is Account Products?
Where is Avail Products?
And where is

Account Product Missing

And where are these other accounts.

And are you willing to use Vba
​

There would be 3 tables, on 3 separate tabs. the account;product table, the available products table, and a results table that contains account and missing products. Id try VBA, Is this the info you are looking for. Note the tables have much more data, i'm interested in find the difference for all accounts in one results table.

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

<colgroup><col><col></colgroup><tbody>
</tbody>

Available Products

Apple

Orange

Tomato

Spinach

Garlic Results Table Account Product Missing 1 spinach 1 garlic 2 apple 2 tomato <colgroup><col><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col span="2"><col><col span="3"><col><col><col></colgroup><tbody></tbody>

 

[MoshiM]

@mather7

You didn't answer @My Aswer Is This 's questions. What are the sheet names and is your data in an actual table or is it just a range. If it's a table what is its name ? Where within the worksheet or table would you find the data ex: columns/rows 1 and 2 of the table or the range within the worksheet ?

 

[stunnrock]

I'm sure this can be done a lot more efficiently, but I've spent more time than I intended on this, so haven't started to reduce it. I have assumed that you have a list of Accounts in A2:A10, a list of Products in C2:C10, and your list of Accounts with Products in E2:F10.


In H1:
=COUNTA($C$2:$C$10)

In H2:
=IF($A2="",0,H$1-COUNTIF($E$2:$E$10,$A2))

In I2:
=IF($H2,I1+H2,0)

In K2:
=INDEX($A$2:$A$10,MATCH(MIN(INDEX($I$2:$I$10+(ROWS($I$2:$I$10)*($I$2:$I$10<row()-row(k$1))),)),$i$2:$i$10,0))


In L2 (this is a single cell array entered formula, see below for more info):
=INDEX($C$2:$C$10,MATCH(0,NOT(ISNA(MATCH($C$2:$C$10,IF($E$2:$E$10=$K2,$F$2:$F$10,0),0)))+COUNTIF(INDEX(L$1:L1,MATCH($K2,$K$1:$K2,0)-(COUNTIF($K$1:$K2,$K2)=1)):L1,$C$2:$C$10),0))


Now copy H2:L2 as far down as required.


[When you type the formula in L2, it needs to be finished with Ctr+Shift+Enter, not just Enter. When this is done correctly, the formula will be bracketed with {} Be careful if you edit this at a later date, you must remember to array-enter it.]</row()-row(k$1))),)),$i$2:$i$10,0))

 

[mather7]

@mather7

You didn't answer @My Aswer Is This 's questions. What are the sheet names and is your data in an actual table or is it just a range. If it's a table what is its name ? Where within the worksheet or table would you find the data ex: columns/rows 1 and 2 of the table or the range within the worksheet ?

Sorry i wasn't clear hope this helps.

Table1 Name = ACT
Contains account information and product
These are the current rows of data, not sure it is need as everything is in a table.
Product (B2:B435);Account (C2:C435)
ACT[#Data],[Product]; ACT[#Data],[Account]

Table2 Name = ID
Contains total Product list
ID[#Data],[ID]

Table3 Name = RTN
Contains the difference between the product list [ID] and the products/account [ACT]
Would like the results put somewhere, I was thinking this table
RTN[#Data],[ProductMissing]; RTN[#Data],[Account]

 

[MoshiM]

Sorry i wasn't clear hope this helps.

Table1 Name = ACT
Contains account information and product
These are the current rows of data, not sure it is need as everything is in a table.
Product (B2:B435);Account (C2:C435)
ACT[#Data],[Product]; ACT[#Data],[Account]

Table2 Name = ID
Contains total Product list
ID[#Data],[ID]

Table3 Name = RTN
Contains the difference between the product list [ID] and the products/account [ACT]
Would like the results put somewhere, I was thinking this table
RTN[#Data],[ProductMissing]; RTN[#Data],[Account]

Are table names and worksheet names the same, do the tables start in cell A1 and are you on Windows?

 

[mather7]

Are table names and worksheet names the same, do the tables start in cell A1 and are you on Windows?

Yes

 

[MoshiM]

Yes

The following assumes that your ID table only has 1 column. If it doesn't then add ".columns(column number within table where product list is found)" before ".value2" in the following

All_Products = .Worksheets("ID").ListObjects("ID").DataBodyRange.Value2 'assumes only 1 column in table

Sub mather7()


Dim ACCT As Object, SpecAcct As Object, All_Products As Variant, _
Queried_Data As Variant, OB As Variant, T As Long, RTN As ListObject

Set ACCT = CreateObject("Scripting.Dictionary")

With ThisWorkbook

    All_Products = .Worksheets("ID").ListObjects("ID").DataBodyRange.Value2 'assumes only 1 column in table
    
    Queried_Data = .Worksheets("ACT").ListObjects("ACT").DataBodyRange.Value2
    
    Set RTN = .Worksheets("RTN").ListObjects("RTN")
    
End With
    'products in B, Accounts in C
With ACCT 'this is a dictionary of Accounts with Account# as key


    For X = 1 To UBound(Queried_Data, 1)
    
        If Not .Exists(Queried_Data(X, 3)) And Queried_Data(X, 3) <> "" Then 'if the account doesn't exist
            
            Set SpecAcct = CreateObject("Scripting.Dictionary")
    
            .Add Queried_Data(X, 3), SpecAcct
            'key of new dictionary will be Account #
        End If
        
        ACCT.Item(Queried_Data(X, 3)).Add Queried_Data(X, 2), Array(Queried_Data(X, 3), Queried_Data(X, 2))
               '  Account# as key    ;   product used as key ;      array is [account number, product]
    Next X
    
    Set SpecAcct = CreateObject("Scripting.Dictionary")
    
    T = 1
    
End With


    For Each OB In ACCT.items ' for each account
    
    With OB
    
        Queried_Data = .keys
        
        For X = 1 To UBound(All_Products, 1)
        
            If Not .Exists(All_Products(X, 1)) And All_Products(X, 1) <> "" Then 'add an array consisting of the accnt # and the missing item
                              
                    SpecAcct.Add T, Array(.Item(Queried_Data(0))(0), All_Products(X, 1))
                                'account # ,  product that wasn't found within dictionary
                    T = T + 1
                
            End If
            
        Next X
        
    End With
            
    Next OB
    
With SpecAcct


    ReDim Final_A(1 To .Count, 1 To 2)
    
    For T = 1 To UBound(.keys) + 1 'place into array
        
        For X = 1 To 2
        
            Final_A(T, X) = .Item(T)(X - 1)
        
        Next X
        
    Next T
        
End With


With RTN.HeaderRowRange 'place on sheet


    .Find("Account", LookIn:=xlValues, LOOKAT:=xlWhole).Offset(1, 0). _
    Resize(UBound(Final_A, 1), 1).Value2 = WorksheetFunction.Index(Final_A, 0, 1)
    
    .Find("ProductMissing", LookIn:=xlValues, LOOKAT:=xlWhole).Offset(1, 0). _
    Resize(UBound(Final_A, 1), 1).Value2 = WorksheetFunction.Index(Final_A, 0, 2)


End With
    
End Sub

 

[MoshiM]

Forgot to clear the 2 Target columns in RTN of previous data

Sub mather7()


Dim ACCT As Object, SpecAcct As Object, All_Products As Variant, _
Queried_Data As Variant, OB As Variant, T As Long, RTN As ListObject, RR As Range


Set ACCT = CreateObject("Scripting.Dictionary")


With ThisWorkbook


    All_Products = .Worksheets("ID").ListObjects("ID").DataBodyRange.Value2 'assumes only 1 column in table
    
    Queried_Data = .Worksheets("ACT").ListObjects("ACT").DataBodyRange.Value2
    
    Set RTN = .Worksheets("RTN").ListObjects("RTN")
    
End With
    'products in B, Accounts in C
With ACCT 'this is a dictionary of Accounts with Account# as key


    For X = 1 To UBound(Queried_Data, 1)
    
        If Not .Exists(Queried_Data(X, 3)) And Queried_Data(X, 3) <> "" Then 'if the account doesn't exist
            
            Set SpecAcct = CreateObject("Scripting.Dictionary")
    
            .Add Queried_Data(X, 3), SpecAcct
            'key of new dictionary will be Account #
        End If
        
        ACCT.Item(Queried_Data(X, 3)).Add Queried_Data(X, 2), Array(Queried_Data(X, 3), Queried_Data(X, 2))
               '  Account# as key    ;   product used as key ;      array is [account number, product]
    Next X
    
    Set SpecAcct = CreateObject("Scripting.Dictionary")
    
    T = 1
    
End With


    For Each OB In ACCT.items ' for each account
    
    With OB
    
        Queried_Data = .keys
        
        For X = 1 To UBound(All_Products, 1)
        
            If Not .Exists(All_Products(X, 1)) And All_Products(X, 1) <> "" Then 'add an array consisting of the accnt # and the missing item
                              
                    SpecAcct.Add T, Array(.Item(Queried_Data(0))(0), All_Products(X, 1))
                                'account # ,  product that wasn't found within dictionary
                    T = T + 1
                
            End If
            
        Next X
        
    End With
            
    Next OB
        
With SpecAcct


    ReDim Final_A(1 To .Count, 1 To 2)
    
    For T = 1 To .Count 'place into array
        
        For X = 1 To 2
        
            Final_A(T, X) = .Item(T)(X - 1)
        
        Next X
        
    Next T
        
End With


With RTN 'place on sheet


    Set RR = .HeaderRowRange.Find("Account", LookIn:=xlValues, LOOKAT:=xlWhole).Offset(1, 0)
    
       T = RR.Column + 1 - .range.Column
       
       .DataBodyRange.Columns(T).ClearContents
       
       RR.Resize(UBound(Final_A, 1), 1).Value2 = WorksheetFunction.Index(Final_A, 0, 1)
       
    Set RR = .HeaderRowRange.Find("ProductMissing", LookIn:=xlValues, LOOKAT:=xlWhole).Offset(1, 0)
    
       T = RR.Column + 1 - .range.Column
       
       .DataBodyRange.Columns(T).ClearContents
    
       RR.Resize(UBound(Final_A, 1), 1).Value2 = WorksheetFunction.Index(Final_A, 0, 2)


End With
    
End Sub