Create List of All Missing Values from 3 Dynamic Worksheets with Criteria

[treemd8025]

Hello,

First, all of you have been a huge help over the years. This is my first time posting, so I hope this is not incorrect in any manner. I appreciate any assistance.

I am creating a VBA automation step for peers that have to manually enter in contract information for each of our clients. There are thousands of clients accounts (Parent Accounts) that have 1 to many sub accounts (child accounts) and there are multiple contracts for each combination of parent/child account. So a parent account with 4 child accounts with four different contracts will have 16 rows of data on the current client contract list.

I have a workbook that contains 3 worksheets:

Sheet1 - The first is a distinct list of all parent and contract combinations which is updated with the most recent list of parents and contracts from sheet3. This may contain new parents or new contracts. I intend to use this as a lookup table to fill in the contract information for any missing contracts on sheet3, but it may not be necessary for you to use it in solution if you can identify distinct parent/contract combinations from sheet3 directly.

Sheet2 - is all the parent and child accounts pulled from the database. This sheet may show new child accounts for an existing parent or show the child accounts for a newly added parent.

Sheet3 shows the most recent list of parent/child/contract combinations, but will be missing any new parents or child accounts.

The idea is that my peers should only have to enter in the parent/contract information going forward, and the workbook will derive the child/contract information from that so they don't have to manually enter each line.

I need to do the following:

1. Identify any missing parent names and add them to sheet3 with all child accounts and contract combinations (As in a new client signed on, they have their own parent account number, 1 to many child accounts, and 1 to many contracts).
2. Identify any missing child accounts and add them to sheet3 with all contract combinations. The parent exists on sheet1 but the child account is new or has always been missing, and as a result the child/contract combination is missing from sheet3.
3. Identify any missing contracts from sheet3. The parent and child exist, but when they entered in the contracts on sheet3 they may have missed one or two.

Other caveats:
-All tables are dynamic.
-Each child account number is unique and only belongs to the one parent.
-All parent accounts are unique.
-There are multiple contract types that can be used by different parents.

Some example data:
Sheet1 lists all parents and contracts:
A B
1 Parent Contract
2 ABC0001 101
3 ABC0001 102
4 13833 103
5 13833 101
6 RBC0001 107

Sheet2 lists all parent and child accounts:
A B
1 Parent Child
2 ABC0001 ABC-1
3 ABC0001 ABC-2
4 13833 Child, LLC.
5 13833 Child 2, LLC.
5 RBC0001 RBC0002
6 RBC0001 RBC0003

Sheet3 lists the most recent list of parent/child/contracts. This table is used to generate sheet1, but if the information below can be used directly in the solution, sheet1 does not need to be included. (ie. if you can identify contract 102 is missing for ABC0001/ABC-2 below without using sheet1 as a lookup or reference, then sheet1 can by bypassed).
A B C
1 Parent Child Contract
2 ABC0001 ABC-1 101*
2 ABC0001 ABC-2 101 *
3 ABC0001 ABC-1 102
4 13833 Child, LLC 103


Missing Values:

Company ABC0001 just started a contract for their ABC-2 account, so ABC0001/ABC-2/102 is not on sheet3, but 101 is on sheet1 under the parent and ABC-2 is on sheet2 under the parent.

Company 13833 just added another child account (Child 2,LLC). So all child/contract combinations for Child 2, LLC need to be added to sheet3. (13833/Child 2,LLC/103 and 13833/Child 2,LLC/101)

Company RBC is new entirely. All parent/child/contract combinations need to be added to sheet3. (RBC0001/RBC0002/107 and RBC0001/RBC0003/107)

Once the new items are added, the updated list is uploaded to the database and next time it becomes the new contract list (sheet3).

I found a previous post that combines all possible combinations between two columns https://www.mrexcel.com/forum/excel...tions-column.html?highlight=cartesian+product, from Andrew Poulsom's comment (thank you, Andrew!) but it combines ALL of the farmers with all of the produce (all farmers have potatoes on their list, not just Mike, all have carrots, not just John..Mike grows apples, oranges, carrots and potatoes, etc.)

   Sub Test()  

Dim Sh As Worksheet
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim Rng3 As Range
    Dim ShNew As Worksheet
    Dim r As Integer
    Dim i As Integer
    Dim ii As Integer
    Set Sh = Worksheets("Sheet1")
    With Sh
        Set Rng1 = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        Set Rng2 = .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
        Set Rng3 = .Range("C2:C" & .Range("C" & .Rows.Count).End(xlUp).Row)
    End With
    Set ShNew = Worksheets.Add
    Sh.Range("A1:C1").Copy ShNew.Range("A1")
    With ShNew
        For i = 1 To Rng3.Rows.Count
            For ii = 1 To Rng1.Rows.Count
                r = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                .Cells(r, 1).Resize(Rng2.Rows.Count).Value = Rng1.Cells(ii, 1).Value
                .Cells(r, 2).Resize(Rng2.Rows.Count).Value = Rng2.Value
                .Cells(r, 3).Resize(Rng2.Rows.Count).Value = Rng3.Cells(i, 1)
            Next ii
        Next i
    End With End Sub

I also tried several iterations of formulas with no success. Examples include:
1. =IF (Countif(sheet3,produce), "OK", "Missing") ----only works if I know the value that is missing and I don't and doesn't look to see if the customer is missing.
2.=IF(ISERROR(VLOOKUP(sheet1!A2,'sheet3'!$A$2:$A$22,1,FALSE)),sheet1!A2,"") ---if I look up a farmer I can find if Mike is missing from the list, if I look up produce, it will look through all produce on sheet2 and find a match because it isn't looking at the farmer's name (one criteria only), same if I look at the customer.


There were quite a few others, but nothing quite matched what I needed.

I appreciate any help and thank you in advance for your time and efforts,


Lori

 

[treemd8025]

I originally was working with Farmers/Customers/Stores for my example, but changed it to something a bit more realistic. I see now I left some Farmer references in there and I can't edit,so this will have to do:

.... from Andrew Poulsom's comment (thank you, Andrew!) but it combines ALL of the farmers with all of the produce (all farmers have potatoes on their list, not just Mike, all have carrots, not just John..Mike grows apples, oranges, carrots and potatoes, etc.)

Should be changed to:
....combines ALL of the parents with all of the contracts, not just those that belong to that one parent.


I also tried several iterations of formulas with no success. Examples include:
1. =IF (Countif(sheet3,produce), "OK", "Missing") ----only works if I know the value that is missing and I don't and doesn't look to see if the customer is missing.

Should be contract and child, respectively.

2.=IF(ISERROR(VLOOKUP(sheet1!A2,'sheet3'!$A$2:$A$22,1,FALSE)),sheet1!A2,"") ---if I look up afarmer (Parent) I can find if RBC0001 is missing from the list, if I look up produce (contract) it will look through all produce (contract) on sheet2 (sheet1) and find a match because it isn't looking at the farmer's name (parent)(one criteria only), same if I look at thecustomer (child)

Basically parents were farmers, customers were child accounts and contracts were produce. Sorry!

 

[treemd8025]

Okay, so after playing around with the code above and changing the fields that get pulled (I pull only the parent, parent/contract (concatenation), and parent/child (concatenation)) using the code and I do get a list of unique combinations. I can then look for a match to the contract file using vlookup.

It works quickly (unlike the array formulas I tried) and it does exactly what I need, but only if I manual run the macro on each parent/client/contract set. If I try it on all parents/contracts/clients I get an overflow error at

 r = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row 

It is making more combinations than I need. I only need it to make the combination when the parent matches the left part of the parent/contract and when the parent matches the left part of the parent/child. Right now, in order to remove the rows that are actually incorrect, I have to put in a formula to see if the parent matches the left of both contract and child columns.

 Sub Test()  

Dim Sh As Worksheet
Dim Sh2 As Worksheet
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim Rng3 As Range
    Dim ShNew As Worksheet
    Dim r As Integer
    Dim i As Integer
    Dim ii As Integer
    Set Sh = Worksheets("parent contracts") 'I have two variables for Sh in my code to pull the one range from the parent/child table.
    Set Sh2=Worksheets("parent child")
    With Sh
        Set Rng1 = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        Set Rng2 = .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
    End with
    With Sh2
        Set Rng3 = .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
    End With
    Set ShNew = Worksheets.Add
    Sh.Range("A1:C1").Copy ShNew.Range("A1")
    With ShNew
        For i = 1 To Rng3.Rows.Count
            For ii = 1 To Rng1.Rows.Count
                r = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                .Cells(r, 1).Resize(Rng2.Rows.Count).Value = Rng1.Cells(ii, 1).Value 'These three lines don't make sense to me.  Why is Range 2 a value and not .cells?
                .Cells(r, 2).Resize(Rng2.Rows.Count).Value = Rng2.Value
                .Cells(r, 3).Resize(Rng2.Rows.Count).Value = Rng3.Cells(i, 1)
            Next ii
        Next i
    End With 
End Sub

Is there a way to alter the above code so that it looks at and creates distinct lists based on matching the three columns I have?

Thank you!

 

[treemd8025]

Okay, so after playing around with the code above and changing the fields that get pulled (I pull only the parent, parent/contract (concatenation), and parent/child (concatenation)) using the code and I do get a list of unique combinations. I can then look for a match to the contract file using vlookup.

It works quickly (unlike the array formulas I tried) and it does exactly what I need, but only if I manually run the macro on each parent/client/contract set. If I try it on all parents/contracts/clients I get an overflow error at

 r = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row 

I can run smaller groups together, but then I have to do cleanup because it combines child accounts from one parent with accounts of another parent, it is making more combinations than I need. I only need it to make the combination when the parent matches the left part of the parent/contract and when the parent matches the left part of the parent/child. Right now, in order to remove the rows that are actually incorrect, I have to put in a formula to see if the parent matches the left of both contract and child columns.

 Sub Test()  

Dim Sh As Worksheet
Dim Sh2 As Worksheet
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim Rng3 As Range
    Dim ShNew As Worksheet
    Dim r As Integer
    Dim i As Integer
    Dim ii As Integer
    Set Sh = Worksheets("parent contracts") 'I have two variables for Sh in my code to pull the one range from the parent/child table.
    Set Sh2=Worksheets("parent child")
    With Sh
        Set Rng1 = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        Set Rng2 = .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
    End with
    With Sh2
        Set Rng3 = .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
    End With
    Set ShNew = Worksheets.Add
    Sh.Range("A1:C1").Copy ShNew.Range("A1")
    With ShNew
        For i = 1 To Rng3.Rows.Count
            For ii = 1 To Rng1.Rows.Count
                r = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                .Cells(r, 1).Resize(Rng2.Rows.Count).Value = Rng1.Cells(ii, 1).Value 'These three lines don't make sense to me.  Why is Range 2 a value and not .cells?
                .Cells(r, 2).Resize(Rng2.Rows.Count).Value = Rng2.Value
                .Cells(r, 3).Resize(Rng2.Rows.Count).Value = Rng3.Cells(i, 1)
            Next ii
        Next i
    End With 
End Sub

Is there a way to alter the above code so that it looks at and creates distinct lists based on matching the three columns I have?

Thank you!