The scenario looks like a clasical “the bot is not me” problem. Such a problem can be identified by checking on following: Is in the requirement description / processing rule definition a part of cognitive action, that is omited?. Because of this lack, the implementation is challenging.
Lets map this to your scenario:
- look up / Join will let us lose 2-Yes, 2-No case, due first found value will be picked up
- the cognitive part is that the eye looks on the downer line, identifies the other line 2-No and is adding this to the report as well.
currently File 1 is nothing else as File 2 without column Quality and is in another order. Now we have to dig more within the requirements and have to look on a case on Row count File 1 is not Row Count File 2. From this case we can further rules.
Maybe following approach can be used and implemented:
- iterate over list 2
- lookup for number in list 1
- if found - use it, if not found use “not found” or leave it empty
- if lookup value is used mark it internaly as used, and do lookup on next not used value
@Soniya215 maybe you can gather some more rules / requirements so @Palaniyappan, me can help you on the setting up the appropriate solution approach