Filtering and reciprocal ties

Feb 24, 2011 at 6:02 AM

In a directed graph is there a way of filtering out all one way ties, therefore only leaving the reciprocated ties?

Feb 24, 2011 at 7:33 PM
Edited Aug 19, 2011 at 5:17 AM

Yes.  Follow the instructions in "Removing Self Loops" at, but call your column "Has Reciprocated Edge" and use this Excel formula:

=IF(AND( IFERROR( MATCH(Edges[[#This Row], [Vertex 1]], [Vertex 2], 0), 0 ) > 0, IFERROR( MATCH(Edges[[#This Row], [Vertex 2]], [Vertex 1], 0), 0 ) > 0), 1, 0)

The formula says "if Vertex 1 exists in the Vertex 2 column AND Vertex 2 exists in the Vertex 1 column, then insert 1 in this column.  Otherwise, insert 0."

[Edit: This is wrong -- see below.]

-- Tony

Aug 19, 2011 at 4:32 AM

Tony ... had a bit of a gap working with this but back on it now.  I think this formula only indicates vertices that have both indegrees and outdegrees? My understanding of a reciprocated tie is that in a directed graph specifically there is a link where A points to B and another link where B points to A. When I pasted in your formula it didn't give me this....but you've given me a start, I'll try and build on your formula and post it back if I can work out how to do it!

Aug 19, 2011 at 5:17 AM
Edited Aug 19, 2011 at 5:21 AM

You're right: my formula is wrong.

The problem is more complicated than I thought at first, and I don't know of a solution that doesn't involve writing a VBA macro.  If you figure out a formula-based solution, please post it.