Removing unwanted rows based on conditions

Question

Hi all,

I have a set of data with repeated rows of transaction reference due to different Trade status.

I've attempted to use Multi-row formula but it's kind of tricky because CountTransaction Reference is not constant. It can be 2,3 or even 4 and I have a set of conditions to follow.

Conditions: Only show trade if same set of transaction reference has Trade Status as "Reconciled", if not "Mismatched", else "Manually Excluded", else "Stale", else "Pending".

The output should only show a single row of Transaction Reference based on above "Trade Status" order manner condition.

Your help is much appreciated, thank you!

Transaction ReferenceRight_Transaction ReferenceCountTransaction ReferenceTrade StatusTrade Reconciliation TimeAAA_1S234567AAA_1S2345673Manually Excluded AAA_1S234567AAA_1S2345673Reconciled7/3/2024 4:02AAA_1S234567AAA_1S2345673Stale CCC_7CJD1077519881CCC_7CJD10775198812Mismatched CCC_7CJD1077519881CCC_7CJD10775198812Stale AAA_91351075370961AAA_913510753709612Pending AAA_91351075370961AAA_913510753709612Mismatched AAA_AE931079772401AAA_AE9310797724012Mismatched AAA_AE931079772401AAA_AE9310797724012Reconciled7/29/2024 5:37AAA_9B871075521941AAA_9B8710755219412Pending AAA_9B871075521941AAA_9B8710755219412Reconciled7/1/2024 7:39BBB_7BE01076118891BBB_7BE010761188912Reconciled7/3/2024 13:03BBB_7BE01076118891BBB_7BE010761188912Stale AAA_9C711076907621AAA_9C7110769076212Manually Excluded AAA_9C711076907621AAA_9C7110769076212Stale BBB_4G875615BBB_4G8756153Mismatched BBB_4G875615BBB_4G8756153Reconciled7/19/2024 15:37BBB_4G875615BBB_4G8756153Stale AAA_49833D5AAA_49833D53Manually Excluded AAA_49833D5AAA_49833D53Reconciled7/3/2024 8:16AAA_49833D5AAA_49833D53Stale CCC_851076A7ZCCC_851076A7Z3Mismatched CCC_851076A7ZCCC_851076A7Z3Pending CCC_851076A7ZCCC_851076A7Z3Reconciled7/16/2024 3:57

perin · Accepted Answer

Hello @meilingg

Please see the example attached, you can create a support input text file based on your instructions to put the records in the correct sort order. After that you can sort the records based on the support table and group it by transaction reference and CountTransaction Reference and get only the first record considering trade status and trade reconciliation time for each group.

Hope it helps.

Best,

Gustavo.

OTrieger · Accepted Answer

Hello @meilingg

Another way will be to use a Formula tool, set Flags based on your conditions, "Reconciled"=1, "Mismatched" = 2 etc, using ELSEIF. Ensure that Flag is integer. Then use a Summarize tool Grouping based Transaction Reference and Min Flag. This will give you a list of the Min numbers foe each transaction, now use Find and replace to return the original names of the Trade Status, by reconnecting the output of the Flagging that you did in the Formula tool. So with these 3 tools you will get that result, Formula tool to create Flags, Summarize Tool to get Min value and Find and Replace Tool to get back the original status.

Kind regards,,
Oren