I have to compare 2 tables based on complex business rules. I feel it is doable thru dynamic array formula, tried few options, but not getting the required results. Is it too much to do with formula? Any suggestions from experts?
'source' table has the base data. It should be compared with 'spTable' based on the following business rules. Outcome of comparison should be listed in 'compare' column in both tables.
Rules -
1. Compare the 'desc' in 'spTable' for each 'id' with 'desc' & 'id' from 'source' table. Note that 'desc' is in single row in 'source' table & is in multiple rows in 'spTable'.
2. If 'desc' matches between 2 tables, enter 'match' in the compare column in both tables.
3. Check the mismatches between 2 tables. If there is a mismatch, enter 'mismatch' in 'spTable' & 'mismatch-' followed by mismatched element in 'source' table.
a. Mismatch due to items missing from desc - For example, refer id 1. 'source' table has dd,aa,mm. 'spTable' has mm, dd only. Since these match individually between tables, these are marked as 'match' in 'spTable'. But aa is missing. So, it is marked as 'mismatch-aa' in 'source' table.
b. Mismatch due to incorrect items - For example, refer id 5. Number of items are correct, but one of the item is incorrect. 'source' table has m6t but 'spTable' has mt. In the 'spTable' enter mismatch against id 5 & desc mt. In the 'source' table, enter 'incorrect-m6t'.
4. If items from 'source' table are not found in 'spTable', don´t enter any comments in 'source' table.
Conditional format -
As a next step, want to know if it is possible to embed conditional format within formula. If so, how can this be done? I´m anyway doing the conditional format separately. If it can also be combined within formula, it will be a bonus. Otherwise, it is enough if the calculations mentioned above are correctly done.
'source' table:
'spTable'
'source' table has the base data. It should be compared with 'spTable' based on the following business rules. Outcome of comparison should be listed in 'compare' column in both tables.
Rules -
1. Compare the 'desc' in 'spTable' for each 'id' with 'desc' & 'id' from 'source' table. Note that 'desc' is in single row in 'source' table & is in multiple rows in 'spTable'.
2. If 'desc' matches between 2 tables, enter 'match' in the compare column in both tables.
3. Check the mismatches between 2 tables. If there is a mismatch, enter 'mismatch' in 'spTable' & 'mismatch-' followed by mismatched element in 'source' table.
a. Mismatch due to items missing from desc - For example, refer id 1. 'source' table has dd,aa,mm. 'spTable' has mm, dd only. Since these match individually between tables, these are marked as 'match' in 'spTable'. But aa is missing. So, it is marked as 'mismatch-aa' in 'source' table.
b. Mismatch due to incorrect items - For example, refer id 5. Number of items are correct, but one of the item is incorrect. 'source' table has m6t but 'spTable' has mt. In the 'spTable' enter mismatch against id 5 & desc mt. In the 'source' table, enter 'incorrect-m6t'.
4. If items from 'source' table are not found in 'spTable', don´t enter any comments in 'source' table.
Conditional format -
As a next step, want to know if it is possible to embed conditional format within formula. If so, how can this be done? I´m anyway doing the conditional format separately. If it can also be combined within formula, it will be a bonus. Otherwise, it is enough if the calculations mentioned above are correctly done.
'source' table:
excel problems.xlsx | |||||
---|---|---|---|---|---|
B | C | D | |||
2 | id | desc | compare | ||
3 | 1 | dd,aa,mm | mismatch-aa | ||
4 | 2 | bbb | |||
5 | 3 | kk,tt,cc,2e | |||
6 | 4 | p,yu | |||
7 | 5 | ij,gt,fx,w,m6t | mismatch-m6t | ||
8 | 6 | h | |||
9 | 7 | df,ao | |||
10 | 8 | pmk,q,88,un,zt | |||
11 | 9 | ec,bl | match | ||
12 | 10 | 72,hp,fv | match | ||
Sheet8 |
'spTable'
excel problems.xlsx | |||||
---|---|---|---|---|---|
F | G | H | |||
2 | id | desc | compare | ||
3 | 9 | bl | match | ||
4 | 9 | ec | match | ||
5 | 1 | mm | match | ||
6 | 1 | dd | match | ||
7 | 5 | w | match | ||
8 | 5 | fx | match | ||
9 | 5 | mt | mismatch | ||
10 | 5 | ij | match | ||
11 | 5 | gt | match | ||
12 | 10 | hp | match | ||
13 | 10 | 72 | match | ||
14 | 10 | fv | match | ||
Sheet8 |