Offset Same amounts

[BRRLL99]

I have following data

Amount	ID
-400	100A
-400	100A
400	100A
-400	100A
100	100A
-100	100A
10	100A
5	100A
6	100A
-6	100A
-5	100A
-10	100A
-5	100A
-5	100A
5	100A
99	100A

I have tried this approach to get the expected output
Solved: Multi-Row level Analysis - Alteryx Community, My data has duplicates with positive and negative this approach is not working out

Expected output: positive number should be matched with negative number

Amount	ID	MATCH
-400	100A
-400	100A
400	100A	M
-400	100A	M
100	100A	M
-100	100A	M
10	100A	M
5	100A	M
6	100A	M
-6	100A	M
-5	100A	M
-10	100A	M
-5	100A
-5	100A	M
5	100A	M
99	100A

[Rhys_Cooper · Accepted solution]

Hi @BRRLL99 , thank you for your quesiton - this was an interesting one to figure out. Please find my steps, screenshots and solution all below

Method:

1.  Filter positive and minus values using
2. Calculate absolute amount of minus values to create possible pairs with positive values
3. Use multirow forumal on the positive set of values and negative set of values to give each independant index value. For example 5,5 would have the index of  0,1. note this is zero indexed. The purpose of adding this index is to stop an exploding join since both the value, and its correspoding index number are used in the join condition.
4. The output left and right anchors of said join will contain values who cant pair up. this is either because there isnt enough values left to pair withit, or none at all.
5. Use the formula tool to attach a match flag. ones which didnt join are left with a null match flag
6. Transpose this stream so each pair gets two rows
7. Union all streams back onto each other and add back in the ID

Note: the outputs are identifcal to your desired, but are in a slightly different order

any questions or adjustments (this isnt dynamic for more than one ID type) do let me know and please choose this is the solution if it was what you are after!

regards- Rhys Cooper