Remove Reciprocal data rows

[RachRoberts]

Hi there,

I've been grappling with a problem all afternoon and wonder if someone might be able to help me.

I have data for person relationships where there is a 'duplicate' entry of the reciprocal data row in my file.

Person ID	Relationship	Related Person ID	Reciprocal Relationship
123	Parent	456	Child
456	Child	123	Parent
789	Advisor	888	Advisee
888	Advisee	789	Advisor

I need the relationship between Person ID 123 and 456 to appear only once, and the same goes for the relationship between 888 and 789.

The problem is that I don't have a way (currently!) to order them as the records are shown here.  If I could, then the solution would be relatively simple, e.g. using the Multi-Row Formula tool.

Can anyone help me?

Any advice would be greatly appreciated!

Rachel

[atcodedog05]

Hi @RachRoberts 

Here is how you can do it.

Workflow:

Hope this helps : )

[BrandonB]

I think this should do it for you: 

Workflow attached

[BrandonB]

@atcodedog05 amazing solution! I created my key via transpose/sort/concat, but yours is the same concept just way more elegant. 

[RachRoberts · Accepted solution]

Thank you so much!  Goodness knows why I couldn't work that out - a HUGE help to a tired brain, thanks again :)

[atcodedog05]

Happy to help : ) @RachRoberts 

If my response helps please don't forget to mark it as solution.

Cheers and have a nice day!