Hello, as I mentioned last week I would be posting this. This is very similar to my post last week about comparing two lists and generating a third list of items that were missing. Well this time, I am looking to compare two lists and generate a third list of what is different from List 1. Periodically, we do audits on employee information between two systems to make sure they match. There are actually two different audits that we do but if I can get a solution for this one, then I can apply that the second one which has different fields, but the same number of comparisons.
SCENARIO
We are doing an audit between List 1 and 2 and looking for differences in position codes and/or pay rates. List 3 has the info of those who have something different between the two lists and lists them accordingly. John Smith1 has a different position code and a different pay rate. John Smith2 has a different position code but his pay rate is the same. John Smith4 has the same position code between the two lists but a different pay rate. So their names would be generated because one or BOTH of those fields has something different from the first list.
I'm pretty sure the formula here would utilize Aggregate but that is a function I am not at all familiar with so I definitely need help on that.
SCENARIO
| A | B | C | D | |
| 1 | List 1 | |||
| 2 | Employee Name | Employee ID | Position Code | Rate of Pay |
| 3 | John Smith 1 | 1234567 | 10305275 | $ 20.00 |
| 4 | John Smith 2 | 2345678 | 10305276 | $ 20.00 |
| 5 | John Smith 3 | 3456789 | 10305276 | $ 20.00 |
| 6 | John Smith 4 | 4567890 | 10305275 | $ 20.00 |
| 7 | John Smith 5 | 5678901 | 10305276 | $ 21.00 |
| F | G | H | I | |
| 1 | List 2 | |||
| 2 | Employee Name | Employee ID | Position Code | Rate of Pay |
| 3 | John Smith 1 | 1234567 | 10305276 | $ 21.00 |
| 4 | John Smith 2 | 2345678 | 10305275 | $ 20.00 |
| 5 | John Smith 3 | 3456789 | 10305276 | $ 20.00 |
| 6 | John Smith 4 | 4567890 | 10305275 | $ 20.50 |
| 7 | John Smith 5 | 5678901 | 10305276 | $ 21.00 |
| K | L | M | N | |
| 1 | List 3 | |||
| 2 | Employee Name | Employee ID | Position Code | Rate of Pay |
| 3 | John Smith 1 | 1234567 | 10305276 | $ 21.00 |
| 4 | John Smith 2 | 2345678 | 10305275 | $ 20.00 |
| 5 | John Smith 4 | 4567890 | 10305275 | $ 20.50 |
We are doing an audit between List 1 and 2 and looking for differences in position codes and/or pay rates. List 3 has the info of those who have something different between the two lists and lists them accordingly. John Smith1 has a different position code and a different pay rate. John Smith2 has a different position code but his pay rate is the same. John Smith4 has the same position code between the two lists but a different pay rate. So their names would be generated because one or BOTH of those fields has something different from the first list.
I'm pretty sure the formula here would utilize Aggregate but that is a function I am not at all familiar with so I definitely need help on that.
