Removing unwanted rows based on conditions

[meilingg]

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 Reference	Right_Transaction Reference	CountTransaction Reference	Trade Status	Trade Reconciliation Time
AAA_1S234567	AAA_1S234567	3	Manually Excluded
AAA_1S234567	AAA_1S234567	3	Reconciled	7/3/2024 4:02
AAA_1S234567	AAA_1S234567	3	Stale
CCC_7CJD1077519881	CCC_7CJD1077519881	2	Mismatched
CCC_7CJD1077519881	CCC_7CJD1077519881	2	Stale
AAA_91351075370961	AAA_91351075370961	2	Pending
AAA_91351075370961	AAA_91351075370961	2	Mismatched
AAA_AE931079772401	AAA_AE931079772401	2	Mismatched
AAA_AE931079772401	AAA_AE931079772401	2	Reconciled	7/29/2024 5:37
AAA_9B871075521941	AAA_9B871075521941	2	Pending
AAA_9B871075521941	AAA_9B871075521941	2	Reconciled	7/1/2024 7:39
BBB_7BE01076118891	BBB_7BE01076118891	2	Reconciled	7/3/2024 13:03
BBB_7BE01076118891	BBB_7BE01076118891	2	Stale
AAA_9C711076907621	AAA_9C711076907621	2	Manually Excluded
AAA_9C711076907621	AAA_9C711076907621	2	Stale
BBB_4G875615	BBB_4G875615	3	Mismatched
BBB_4G875615	BBB_4G875615	3	Reconciled	7/19/2024 15:37
BBB_4G875615	BBB_4G875615	3	Stale
AAA_49833D5	AAA_49833D5	3	Manually Excluded
AAA_49833D5	AAA_49833D5	3	Reconciled	7/3/2024 8:16
AAA_49833D5	AAA_49833D5	3	Stale
CCC_851076A7Z	CCC_851076A7Z	3	Mismatched
CCC_851076A7Z	CCC_851076A7Z	3	Pending
CCC_851076A7Z	CCC_851076A7Z	3	Reconciled	7/16/2024 3:57

[perin · Accepted solution]

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 solution]

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

[meilingg]

Thanks a million Gustavo, you are brilliant!

[meilingg]

Thank Oren! That's very helpful too, appreciate both!