Hi Everyone,
I am having issue with transforming this data in a usable format. I have tried the Multi-Row but was getting false positive, when I tried Transpose tool I ended up with more than the 1 or 2 verifiers per record.
The aim is for each transaction that we are to identify the final 1 or verifier. If a transaction is more than or equal to 20000 it should have 2 verifiers otherwise it should be flagged as an anomaly. See below table for desired output.
Your assistance would be greatly appreciated.
| DATE | ID | Amount | Error Type | Action Date | Verifier 1 | Verifier 2 | Flag |
| 20/10/2025 | BT17203 | 20000 | Failure | 20/10/2025 11:21 | Bob | Barbara | N |
| 13/08/2025 | BT46848 | 10000 | Detection | 13/08/2025 12:10 | Amber | | N |
| 13/08/2025 | BT64846 | 40000 | Detection | 13/08/2025 16:52 | Denise | | N |
| 14/08/2025 | BT73709 | 40 | Closed | 23/09/2025 10:09 | Victor | | N |
| 14/08/2025 | BT401364 | 1000 | Repair | 14/08/2025 14:47 | Sandy | | N |
| 15/08/2025 | BT439896 | 100 | Repair | 15/08/2025 13:42 | Sam | | N |
| 19/08/2025 | BT539341 | 6000 | Repair | 19/08/2025 17:33 | Mark | | N |
| 03/10/2025 | BT444447 | 7500 | Missing | 03/10/2025 13:52 | Vernon | Jackie | N |
| 20/10/2025 | BT17205 | 40000 | Failure | 20/10/2025 13:30 | Bob | | Y |
