Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Find Missing Values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Post Find Missing Values

    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.

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,831
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Find Missing Values

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    New Member
    Join Date
    Jun 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Missing Values

    Quote Originally Posted by My Aswer Is This View Post
    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

    Available Products
    Apple
    Orange
    Tomato
    Spinach
    Garlic

    Results Table
    Account Product Missing
    1 spinach
    1 garlic
    2 apple
    2 tomato


  4. #4
    Board Regular
    Join Date
    Jan 2018
    Posts
    234
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Missing Values

    @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 ?
    Last edited by MoshiM; Aug 15th, 2019 at 04:34 PM.

  5. #5
    Board Regular
    Join Date
    Dec 2017
    Posts
    61
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Missing Values

    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

    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.]
    Last edited by stunnrock; Aug 15th, 2019 at 04:54 PM.

  6. #6
    New Member
    Join Date
    Jun 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Missing Values

    Quote Originally Posted by MoshiM View Post
    @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]

  7. #7
    Board Regular
    Join Date
    Jan 2018
    Posts
    234
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Missing Values

    Quote Originally Posted by mather7 View Post
    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?
    Last edited by MoshiM; Aug 15th, 2019 at 06:10 PM.

  8. #8
    New Member
    Join Date
    Jun 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Missing Values

    Quote Originally Posted by MoshiM View Post
    Are table names and worksheet names the same, do the tables start in cell A1 and are you on Windows?
    Yes

  9. #9
    Board Regular
    Join Date
    Jan 2018
    Posts
    234
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Missing Values

    Quote Originally Posted by mather7 View Post
    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
    Code:
    All_Products = .Worksheets("ID").ListObjects("ID").DataBodyRange.Value2 'assumes only 1 column in table
    Code:
    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
    Last edited by MoshiM; Aug 15th, 2019 at 08:05 PM.

  10. #10
    Board Regular
    Join Date
    Jan 2018
    Posts
    234
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find Missing Values

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

    Code:
    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
    Last edited by MoshiM; Aug 15th, 2019 at 10:08 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •