Forum Discussion
How to validate completeness of 1 list against another list
Is there a list that contains all possible combinations of accounts, types and composites? If so it should be possible to check under which account a certain combination of type and composite is missing. Otherwise where do we know from that ABC2 is missing composite "XYZ2" and ABC is missing XYZ1 ?
In the sample data there are accounts ABC1, ABC2, ABC3 and ABC4 but i don't see ABC. Does ABC mean any of the hundreds of accounts ?
OliverScheurich Apologies. I made a typo on my last sentence in the original post. I was typed just "ABC" but I meant to say "ABC2" is missing composite "XYZ2".
I can make a complete list of all possible combinations of accounts, types, and composites but I was also trying to figure out a way to do that easily. I have a complete list of types and composites which is list 2. Now if I add in the accounts is there a way to have excel create a row for each account and composite that it should be based on the type. For example if I have 5 accounts with a "Private" type, is there a way to have excel make for each of those 5 accounts then have a row for all composites that it should include which would be "XYZ1", "XYZ2", and "PV1" in this example. If I could make that list then the lookup functions would work.
Just trying to figure out how to do this with 400 accounts and basically checking that those accounts are included in all the appropriate composites base on types.
Sub accounts() Dim j As Integer Dim i As Integer Dim k As Integer Range("J:L").Clear k = 2 j = Range("A" & Rows.Count).End(xlUp).Row For i = 2 To j Select Case Cells(i, 2) Case Is = "expand" Range(Cells(k, 10), Cells(k + 2, 10)).Value = Cells(i, 1).Value Range(Cells(k, 11), Cells(k + 2, 11)).Value = Cells(i, 2).Value Range(Cells(k, 12), Cells(k + 2, 12)).Value = Range(Cells(9, 7), Cells(11, 7)).Value Case Is = "open" Range(Cells(k, 10), Cells(k + 3, 10)).Value = Cells(i, 1).Value Range(Cells(k, 11), Cells(k + 3, 11)).Value = Cells(i, 2).Value Range(Cells(k, 12), Cells(k + 3, 12)).Value = Range(Cells(5, 7), Cells(8, 7)).Value Case Is = "private" Range(Cells(k, 10), Cells(k + 2, 10)).Value = Cells(i, 1).Value Range(Cells(k, 11), Cells(k + 2, 11)).Value = Cells(i, 2).Value Range(Cells(k, 12), Cells(k + 2, 12)).Value = Range(Cells(2, 7), Cells(4, 7)).Value End Select k = Range("J" & Rows.Count).End(xlUp).Row + 1 Next i End SubWith this code you can make the complete list of 400 accounts. In columns A and B you can enter the accout and the type as shown in the attached file. Then you can click the button in cell H2 to start the macro. After this you can compare another list of accounts, types and composites with the complete list in columns J, K and L.