Hi all,
I need to figure out a way to filter out duplicate values (Trade ID) but they're contingent on several other categories. There are a few scenarios;
1) First scenario, I have the below table, all values are duplicate;
| Trade ID | Book CD | Dbo Fx Daily Trx.Trade Date | Time Period.Trade Date | Buy/Sell | CCY Pair | CCY1 | CCY2 | Dbo Fx Daily Trx.Counter Notional | Dbo Fx Daily Trx.Settlement Date | Dbo Fx Daily Trx.Trade Status | Primary Notional Value | Time Period.Settlement Date | Fx Trade.Trade Status | Sds Id | ID | Sap Company CD | Country CD | Country Nm | Trade Mnth |
| T123456 | Cheese | 3/8/2022 | 8-Mar-22 | Buy | USD-JPY | USD | JPY | 390431100 | 3/27/2023 | AMEND | 3000000 | 27-Mar-23 | AMEND | 42540785 | Bush | 5279 | SG | Singapore | 2023-03 |
| T123456 | Cheese | 3/8/2022 | 8-Mar-22 | Buy | USD-JPY | USD | JPY | 390431100 | 3/27/2023 | AMEND | 3000000 | 27-Mar-23 | AMEND | 42540785 | Bush | 5279 | SG | Singapore | 2023-03 |
Since Trade ID's are duplicates, they will be filtered out. But they will need to be filtered to Tab 1, which is only for Trade ID's with only values that are duplicate for all.
2) For the table below, Trade ID's are duplicate, but some fields are not. "Book CD" and "Dbo Fx Daily Trx.Counter Notional" are of different values (Highlighted in pink) These will need to be filtered out to Tab 2;
| Trade ID | Book CD | Dbo Fx Daily Trx.Trade Date | Time Period.Trade Date | Buy/Sell | CCY Pair | CCY1 | CCY2 | Dbo Fx Daily Trx.Counter Notional | Dbo Fx Daily Trx.Settlement Date | Dbo Fx Daily Trx.Trade Status | Primary Notional Value | Time Period.Settlement Date | Fx Trade.Trade Status | Sds Id | ID | Sap Company CD | Country CD | Country Nm | Trade Mnth |
| T4513245 | Mochi | 3/8/2022 | 8-Mar-22 | Sell | USD-JPY | USD | JPY | 390431100 | 3/27/2023 | AMEND | 3000000 | 27-Mar-23 | AMEND | 42540785 | Sally | 7740 | SG | Singapore | 2023-03 |
| T4513245 | Bear | 3/8/2022 | 8-Mar-22 | Sell | USD-JPY | USD | JPY | 390431200 | 3/27/2023 | AMEND | 3000000 | 27-Mar-23 | AMEND | 42540785 | Sally | 7740 | SG | Singapore | 2023-03 |
If any of these below categories are different, but the Trade ID is duplicate, then they will need to go to the same separate tab;
The list:
- Book CD
- Dbo Fx Daily Trx.Trade Date
- Time Period.Trade Date
- Buy/Sell
- CCY Pair
- CCY1
- CCY2
- Dbo Fx Daily Trx.Counter Notional
- Dbo Fx Daily Trx.Settlement Date
- Dbo Fx Daily Trx.Trade Status
- Primary Notional Value
- Time Period.Settlement Date
- Fx Trade.Trade Status
- Sds Id
3) Final scenario is when Trade ID and all of the categories listed below are duplicates, but categories not found in the list are not duplicates
The list:
- Book CD
- Dbo Fx Daily Trx.Trade Date
- Time Period.Trade Date
- Buy/Sell
- CCY Pair
- CCY1
- CCY2
- Dbo Fx Daily Trx.Counter Notional
- Dbo Fx Daily Trx.Settlement Date
- Dbo Fx Daily Trx.Trade Status
- Primary Notional Value
- Time Period.Settlement Date
- Fx Trade.Trade Status
- Sds Id
The table below shows Trade ID and all categories above as duplicates;
| Trade ID | Book CD | Dbo Fx Daily Trx.Trade Date | Time Period.Trade Date | Buy/Sell | CCY Pair | CCY1 | CCY2 | Dbo Fx Daily Trx.Counter Notional | Dbo Fx Daily Trx.Settlement Date | Dbo Fx Daily Trx.Trade Status | Primary Notional Value | Time Period.Settlement Date | Fx Trade.Trade Status | Sds Id | ID | Sap Company CD | Country CD | Country Nm | Trade Mnth |
| T280338740 | Bird | 3/24/2022 | 24-Mar-22 | Buy | USD-CNH | USD | CNH | 323650000 | 3/28/2023 | AMEND | 50000000 | 28-Mar-23 | AMEND | 40282022 | John | 5279 | SG | Singapore | 2023-03 |
| T280338740 | Bird | 3/24/2022 | 24-Mar-22 | Buy | USD-CNH | USD | CNH | 323650000 | 3/28/2023 | AMEND | 50000000 | 28-Mar-23 | AMEND | 40282022 | Megan | 5278 | SG | India | 2023-03 |
However, for the categories highlighted in orange and underlined, some of them are not duplicates.
Sap Company CD
Country CD
Country Nm
Trade Mnth
The results from this scenario will be filtered out to Tab 3.
-----------
I have tried several filters/formulas but have found this to be quite challenging getting all of us done in a single step. The formula I'm currently using somehow ignores several key aspects, so I'm abit stuck here
Appreciate the help!
Best,
