Compare all items with certain criteria against each other and analyse frequency/patterns

[PeterAP]

I have a data source (sample data attached) in which I'd like to compare the amounts of all items meeting certain criteria (match on ref 1 and ref 2) against each record in that group. 

I expect that this would create a bigger data set as I've shown under "Expected Data Set".

I thought of using the multi-row formula tool, but I think this only compares items within x number of rows and not a dynamic number of rows. Is anyone able to help with a possible solution?

Following on from that, I would then like to analyse the frequency/patterns of those differences. Does any one have any tools that they would recommend to demonstrate this?

[randreag · Accepted solution]

hi @PeterAP 

If I am undestanding your problem , you could use the join tool and then filter. I attached a small example.

And for the las table you could use a summarize tool.

I hope it helps

[PeterAP · Accepted solution]

Thanks @randreag!

I didn't really think of using the join tool in this scenario, but it works, thank you!

I've attached the final sample workflow in case anyone else finds this useful. In this, I've also added in a frequency table.