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

Question

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?

Analysis of Differences.xlsx

randreag · Accepted Answer

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 Answer

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.