How to compare different lines against each other for complex filtering?
I've joined two sets of data and the second set of data has Numbers that match the first set of data in some cases and in some cases there were corrections made. When I do the join, 2 line items become 4, because in many cases I'm duplicating data and need to filter it out, but in some cases like the below it wasn't exactly duplicated because there was a change for DataSet 1 to DataSet 2. DataSet 2 is the corrected DataSet, but I need to be able to compare and see what changes were made from DataSet 1 to DataSet 2.
In the below scenario. The final product I need is line 4 and line 1 to remain and lines 2 and 3 to be filtered out. Line 4 is easy to identify because there is a perfect match between the two Sets. Line 1 however requires me to compare data lines to one another ie (I need to compare line 3 to line 4 to one another to filter out line 3 and then I need to compare lines 1 and 2 to line 4 to then filter out line 2)
Its easy enough to find the matches and then add a match identifier column to compare against, but I've been unable to find a way to compare one line against another.
Encounter Id. Set 1 Date x Date y Set 2
52433771 2 99306 2022-09-30 2022-10-01 99310
52433771 2 99306 2022-09-30 2022-10-01 99497
52433771 4 99497 2022-09-30 2022-10-01 99310
52433771 4 99497 2022-09-30 2022-10-01 99497
I hope this makes sense, I found it difficult to describe. Please let me know if additional information or clarification is required.