A database containing sales info needs to be cleaned where:
- exact duplicates need to be removed;
- duplicates where a sales type is different but sale price, year, and quarter are the same;
- duplicates where a sales type is different and quarter is different, but price is the same;
- duplicates where year, quarter, sale type are the same, but sale price increases. The entry with the higher value should remain.
Properties that are sold in a different year at a different price should remain. Properties that are sold in the same year at different quarters and are both S11 should remain. S11 sale type will be kept over other sale types where a duplicate exists. There are many adjoining columns containing additional info that need to kept intact. It would also be handy to view which entries have been removed.
Current Data
| Sale Date | Sale Year | Sale Quarter | Sale Type | Sale Price | Address | Additional Info |
| 01/12/2021 | 2021 | Q4 | S11 | $30 | 30 Andrew Avenue | x |
| 01/12/2021 | 2021 | Q4 | S11 | $30 | 30 Andrew Avenue | x |
| 01/03/2014 | 2014 | Q1 | S13 | $70 | 123 Stella Drive | x |
| 01/03/2014 | 2014 | Q1 | S11 | $70 | 123 Stella Drive | x |
| 01/05/2010 | 2010 | Q2 | S13 | $55 | 99 Problems Road | x |
| 01/08/2010 | 2010 | Q3 | S11 | $55 | 99 Problems Road | x |
| 01/02/2005 | 2005 | Q1 | S12 | $32 | 99 Problems Road | x |
| 01/11/2019 | 2019 | Q4 | S11 | $64 | 1 Big Crescent | x |
| 01/11/2019 | 2019 | Q4 | S11 | $68 | 1 Big Crescent | x |
Desired Output
| Sale Date | Sale Year | Sale Quarter | Sale Type | Sale Price Final | Address | Additional Info |
| 01/12/2021 | 2021 | Q4 | S11 | $30 | 30 Andrew Avenue | x |
| 01/03/2014 | 2014 | Q1 | S11 | $70 | 123 Stella Drive | x |
| 01/05/2010 | 2010 | Q3 | S11 | $55 | 99 Problems Road | x |
| 01/02/2005 | 2005 | Q1 | S12 | $32 | 99 Problems Road | x |
| 01/11/2019 | 2019 | Q4 | S11 | $68 | 1 Big Crescent | x |
Would really appreciate some help with this - even a partial solution / method of action for some of the cleaning would be great! Many thanks.
