How to identify difference in values and give result based on certain criteria?

[rizwanulhasan]

Hi all,

Hope all are well.

In attached file, there are 3 sheets.

Source Data Sheet contains 2 columns - Trade Item Number and Market. The numbers can have multiple codes in same column with different values in another column (highlighted in yellow).

Ex. In rows, 79, 275, 503 and 638, 06291107430881 is linked with codes KW, OM, JO and QA.


Sheet System Data contains 3 columns - Trade Item Number , Primary Market and Additional Market. The column Trade Item Number can have repeated numbers in same column with different values in another 2 columns (highlighted in yellow).

Ex. In rows 1049 to 1050, 06291107430881 is linked with code QA and BH.

The aim is to find the missing markets for a trade item number given in sheet System Data with respective to actual sheet Source Data.

Ex. 06291107430881 should in actual have 4 codes linked i.e. KW, OM, JO and QA (as per Source Data) but in actual it has only 2 codes QA and BH linked. Out of the required 4 codes, it only has QA. So, the missing codes are KW, OM and JO.

The results should be populated in sheet Result such that:

1. Only the trade item number which has missing market should be populated in sheet Result
2. The trade item number should be in 1st column
3. The combination of markets in sheet source data i.e. KW, OM, JO and QA should be in column “Source Markets” separated by comma.
4. The combination of markets in sheet System data i.e. QA and BH should be in column “System Markets” separated by comma.
5. The difference of Source and System should be in column “Missing Markets” but only the ones missing in column “Source Markets”.

Sample result given in sheet Result.

Note:

1. Its better to first sort column Trade Item Number. But when i do, the format changes and the zeroes prefixed are omitted. In short, column Trade Item Number will always be 14 digits.

2. Sheet System Data might contain duplicate values in 2nd column. Also, sometimes values will only be in 2nd column and not 3rd column.

Can someone please help with the code? Many thanks in advance.