Forum Discussion
How to validate completeness of 1 list against another list
Hi,
Refer to screenshot below. List 2 has for each "Type" there should be the corresponding "Composite"s. Is there a way to validate or point out in List 1 which "Accounts" are missing any "Composite" based on the type and List 2? I can do lookup formulas on list 1 against list 2 to find if there's any composites in List 1 that aren't in List 2 but I'm trying to find what composites are missing in List 1 based off of List 2. Sorry if it seems confusing but kinda hard to explain. For example is there a way to point out that in List 2, ABC is missing composite "XYZ2"?
Thank you!
8 Replies
- Edit on my last statement: For example is there a way to point out that in List 1, ABC2 is missing composite "XYZ2" and to continue to validate down list 1 to point out every instance by account of a missing composite?
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.
=IF(ISNA(VLOOKUP(F3&G3,$B$3:$B$12&$C$3:$C$12,1,FALSE)),"missing in list 1","")Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
- Thanks! That would show me what's missing in list 1 but I'm looking for whats missing in list 1 at the account level. So is there a way through formula or some other means that I can show ABC2 is missing XYZ2 and ABC is missing XYZ1 and on wards assuming there are hundreds of accounts. By doing a lookup on list 2 I can see whats missing but not by each specific account.
