Look for value Within Column

[BRRLL99]

Hi i have following data, this column needs to be matched with negative amount

Logic: in the given example 6800 has negative (6800) in 1:3 ratio then it should be matched
the ratio can be 2:3 or 3:2 or 1:1 etc any combination

@flying008  @ShankerV  @Qiu 

Value
0.00
(0.00)
0.00
6800.00
(6800.00)
(6800.00)
(6800.00)
928.00
(928.00)
(928.00)
928.00
100.00
300.00
400.00
(400.00)

Expected Output:

Value	Match
0.00	M
(0.00)	M
0.00	M
6800.00	M
(6800.00)	M
(6800.00)	M
(6800.00)	M
928.00	M
(928.00)	M
(928.00)	M
928.00	M
100.00
300.00
400.00	M
(400.00)	M

[Qiu · Accepted solution]

@BRRLL99 
It came out a bit length, but I hope it works.

I use the Append tool and filter the match pair.

[ShankerV]

Hi @BRRLL99 

One way of doing this.

Step 1: Record ID tool

Step 2: Filter tool

Contains(ToString([DATA]),'(')

Step 3: Regex tool

Join the True node from Filter tool to Formula tool

Regular Expression: (\d+.\d+)

Output Method: Parse

[ShankerV]

Step 4: Join tool

Join the Formula tool output to L node

False node output or filter tool to R node 

Join by Specific Fields

RegExOut1 -> Data

Step 5: Select tool

From the J node of Join tool, establish the link to 2 select tool

In select tool 1, select only 1st two columns and deselect the remaining

In select tool 2, select only the last 2 columns and deselect the remaining

Step 6: Formula tool

Create a formula tool from both Select tool.

Output column = Match

"M"

Step 7: Union tool

Link L and R node from join tool to Union tool

Then also both Formula tool

So there will be 4 linkages.

Auto Config by Position

Step 8: Unique tool

Select Record ID

Many thanks

Shanker V

[ShankerV · Accepted solution]

Hi @BRRLL99 

I have done minor changes to the previous workflow which is highlighted in Green.

Post the changes, it will fetch the expected output.

Many thanks

Shanker V

[flying008 · Accepted solution]

Hi, @BRRLL99 

[flying008]

Hi, @BRRLL99 

If we can help you get your want, please mark it as a solution for more share.

[binuacs · Accepted solution]

@BRRLL99 batch macro version