Check Row by Row

I have following dataset

Input :

NA	AMT	Brackets
CA	1400.00	1400.00
CA	-1400.00	(1400.00)
4	1400.00	1400.00

Expected Output:
Rules:
1. positive and Negative Same Amount should be matched

2. String and number should be Matched, string and string should not be matched, same with number
in this example string is CA it be can be anything NA, RS etc same with number

NA	AMT	Brackets	match
CA	1400.00	1400.00
CA	-1400.00	(1400.00)	M
04	1400.00	1400.00	M

Data Investigation

Accepted answers

RaphaelSilva

What is your patterns regarding which AMT values can be positive and negative?
Is it expect to match different data with the same AMT value?

The workflow below use ABS values to sort the AMTs, then make sure strings appears first and lastly make negative AMTs shows last, so it can map with the "Numbers"
But I don't know what other rules you have in your data

** Please like the replies that helped you and mark as solution once it solves yours problem

Offset two values.yxmd

All comments

RaphaelSilva

Have you tried creating conditioning labels for the rules you want to match and then join then afterwards?
Like, simplify the data with new formula fields, "is that string or number", is that "negative or positive", "Are values same amount"... then join based on the rules you want, reducing the number of unique values the join tool deal with.

BRRLL99

can you show me how it is done?

I am not aware of conditional labels

RaphaelSilva

I mean, using formula tool you can create labels

IF [AMT] >= 0 THEN "Positive Value" ELSE "Negative Value"
IF [AMT] = [Brackets] THEN "Value Match" ELSE "Different Values"

Things like that, matching with your rules
Then on Join tool you join by those labels

BRRLL99

I tried your logic it's not working

IF [AMT] = [Brackets] THEN "Value Match" ELSE "Different Values"

by default this value will be in different, How can we join on same dataset?

RaphaelSilva

Can you send the sample data and a detailed information on how do you want to match then?
I can create a workflow for you

BRRLL99

I have already attached the sample data

Please check that

RaphaelSilva

I saw it, I just felt you could have more information about that
About the rules too, it is a bit confusing, if you clarify those points I can make a workflow for you

BRRLL99

NA	AMT	Brackets	String/Number
CA	1400.00	1400.00	String
CA	-1400.00	(1400.00)	String	M
4	1400.00	1400.00	Number	M

I have created additional column using formula tool
How can i match string and number when amount is same

BRRLL99

Points are simple

Amount should be same positive and negative 1400 and -1400 and
NA column should have one string and other number

then those should be marked as "M"

RaphaelSilva

Sorry mate, I don't understand your ruling.
What is driving the new column is column NA?
When you say negative values shoould be the same you want to change (1400) to -1400?
Why first record is not a Match? Values Match

BRRLL99

Positive same amount should be setoff with same negative amount

Plus other condition is that NA column should have one string and other number to setoff

CA	-1400.00	M
4	1400.00	M

RaphaelSilva

Okay, I think I got the ask
See if the solution is what you were looking for, I used multi-row formula to find the Matches, used it twice to validate and flag both instances.

Offset two values.yxmd

BRRLL99

can we create ABS and Tile columns, because in my original data set can be mixed

NA	AMT	Brackets	ABS	String/Number	Tile
CA	1400.00	1400.00	1400	String	1
4	-1400.00	(1400.00)	1400	String	1
CA	1400.00	1400.00	1400	Number	1

in the given data it is

It can be

RaphaelSilva

Offset two values.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703