Matching columns and rows to consolidate

[robbcwhite]

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 !!

[JohnJPS · Accepted solution]

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

[Inactive User]

Answer attached.

[robbcwhite]

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 !!

[JohnJPS]

Since you can't download... as a test of my PowerPoint skills, or lack thereof, here's my entire workflow in one slide:

[robbcwhite]

Thanks John!
I actually had it almost figured out (was on the last formula part).
Worked like a charm !!!

This community (and program) rocks !!!
Geek4Life!