Matching columns and rows to consolidate

Hello and a forward thanks for ALL of the great info posted here. I've managed to make it a few months w/o having a scenario where I had to post an 'opportunity' to learn how to do something with this awesome program.

Yet, here I go ! ((Forgive me for not being more 'post savvy' as this is my first time posting)).
I'm trying to in essence, find a 'mirror' image of transactions and 'consolidate' them into a single transaction.

Here's my example:

Point A Point B A$ B$

X C 5 5

Y Z 3 3

Z Y 1 1

Results I'm looking for:

Point A Point B A$ B$

X C 5 5

Y Z 2 2

Because Y Z and Z Y are "opposites" I want to net them together by subtracting the lowest value from the larger in that particular row leaving me with just the consolidated transaction. I was thinking a RegEx_Match somehow would work but I can't seem to figure that one out.

Any suggestions would be marvelously appreciated !!
Thanks !!

Common Use Cases

Accepted answers

JohnJPS

Hi @robbcwhite,

A nice trick for this is to use min/max functions to combine PointA and PointB in such a way that the combination is the same regardless of which one appears first in the data. If I find that flipped them, I switch the A$/B$ values to negative. Then aggregate. Afterward, clean up a bit: flip/flop A and B if the aggregation happens to be negative, etc... this is all in the attached workflow, which, even if it doesn't work for your solution, will hopefully be a good head start.

Cheers,

John

ABorBA.yxmd

All comments

JohnJPS

Hi @robbcwhite,

Cheers,

John

Answer attached.

Thanks for your solution and explanation. I'll give that a whirl and see if it pans out.
Unfortunately, my company does not allow us to download 'solutions/workflows'.
So again, your explanation gave me insights.

Thanks again !!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703