Group Buy or Sell with corresponding opposite transaction based on like data

[jmmart08]

Hi All

I am trying to build a workflow where I can pair column E (TRT) BUY and SELL based on the values in column B, C, D, F, and G being exactly the same.  For example, I would want to group lines 169 and 133 as they have equal values in all of these conditional columns AND are opposites in column E (one is buy, one is sell).  Anything that does not have an opposite transaction (again there should be 1 buy paired/grouped with 1 sell if the criteria columns match) I would just want to kind of separate from the paired groupings.  I am not really sure how to start to try to get to this point.  If anyone can give me any assistance I would really appreciate it.  Thank you so much!

TD	VD	ACCT	SEC	TRT	CP	AMT	MinOfCASH	SumOfPROCEEDS	Price
03-Feb-21	16-Feb-21	505	01F022436	BUY	WF	600,000.00	630,906.25	0	105.05
04-Mar-21	16-Feb-21	505	01F022436	SELL	WF	600,000.00	628,398.44	627773.44	104.63

[Luke_C · Accepted solution]

Hi @jmmart08 

Here's a method you could use.

1. Summarize by the key columns you mentioned and get the count of how many combinations there are for each set of columns. 
2. Join that count back with the original data
3. Filter on records that have a count of 2 (indicating a pair). Anything in the F anchor does not have a corresponding buy/sell record.
4. Sort that data by the key columns so they are in the desired presentation

[jmmart08]

Hi @Luke_C 

This is great.  Thank you very much.  As I was going through this process, I found other variables that I didn't know existed, and was wondering if I may be able to run these by you as I am having trouble solving for these new variables.  There are situations where there are multiple transactions that need to be aggregated together into 1 BUY or SELL based on the criteria discussed in my original post.  The piece that needs to be summed is the amount.  For example, I have attached a new input file where some of the counts are 3 or 4.  I attempted to incorporate these into the summarize tool but I can't seem to get the same result once these pieces are summed together (again using a sum on AMT and grouping by VD, ACCT, SEC, CP, and TRT as Count).  I basically have attempted to do an additional summarize tool but I keep losing the same structure where I have buy/sell as 1 to 1 on the exact match portion.  So there's a population on my false's that I need to account for that I would like to ultimately figure out how to get on my true side that really do have a 1 to 1 match when say the count is higher than 2 but there are 2 buys that match 1 sell so those come as a 3 count so once the AMT is summed they really are a match.  I have attached my workflow that exists now (thanks to your help!!). 

Thank you!

[Luke_C]

Hi @jmmart08 

No problem! Take a look at the attached, I added a second summarize like you mentioned to consolidate the records - I'm not sure what your end result needs to look like but hopefully you can leverage this to get it to the finish line.

[jmmart08]

Hi @Luke_C 

I am looking at this process now.  Thank you VERY much.  I was wondering after I use the summarize tool, is there a way to get additional fields (column headers) in my final output?  There are some fields I want to be displayed in my final output but I don't want them to be grouped together (I don't think) I just want them to be displayed.  Not sure what tool I might be able to use to pull in some of the original fields from the start of my file in addition to those that I used in the summarize tool.  Any help would be so appreciated.  Thanks again.  This is absolutely AMAZING!!

[Luke_C · Accepted solution]

Hi @jmmart08 

That's a little trickier depending on the data you want. I modified the attached to bring the price and net money into the output. Since these vary for the records we're collapsing I used the max and min functions within the summarize tool, but you could change to average or some other operator based on your needs. Is this along the lines of what you're looking for? If the solution was helpful please mark it as accepted 🙂

[jmmart08]

Hi @Luke_C 

That is EXACTLY what I was looking for.  Thank you yet again!

[jmmart08]

Hi @Luke_C 

This is phenomenal.  I have 1 last question if you have the time.  Is there a way to output my True values from my filter into multiple tabs by the CP column?  For example, the value "MS" in the CP column outputs all its' rows onto a single tab in an output, the value "WF" in the CP column outputs all its' rows onto a single tab, etc.

Thank you!!

[Luke_C · Accepted solution]

Hi @jmmart08 

Glad to help! This last part is super straightforward, in an output tool there's an option to leverage a field to name the file or tabs. For excel, choosing the field and selecting 'change file/table name' will result in one tab per value in that field. See attached - just update the file path in the output tool and you should be all set.

[jmmart08]

Hi @Luke_C 

This works beautifully.  Honestly last question.  Is there a tool I can utilize to place multiple blank rows between like sets?  For example in my ACCT column, I would like to pair say ACCT 109 in all instances and place multiple blank rows until the next unique ACCT value comes up, group that pair, etc.  Any suggestions?  Your help has saved hours and hours and hours of manual work.  I cannot thank you enough!!!