Merging duplicate edges NodeXL

May 31, 2011 at 6:09 PM

Although merging duplicate edges is quite simple, I am having massive problems. So any help would be greatly appreciated.

I have created a list of 22 events and the corresponding attendees. Some people have attended more than one event. After importing the data (vertex1 = name and vertex2 = event), I want to merge the duplicates to see from the edge weights how many events a person has been to. When I merge the edges, edge weight comes out as 1, even though I know some people attended up to 5 events. How is this possible? What am I doing wrong?


The only way I ever get edge weights is if I sort the events as well as the names A to Z. The problem then is, however, that the names do not correspond to the correct event.


I hope this all makes sense. I have been trying this for days and I am getting desperate for an answer.


Thanks guys!

May 31, 2011 at 6:34 PM
Edited May 31, 2011 at 10:36 PM

NodeXL's Merge Duplicate Edges feature will not do what you want it to do.  For an explanation of what that feature actually does, hover your mouse over the NodeXL, Data, Prepare Data, Merge Duplicate Edges menu item in the Excel Ribbon.  (I'll include that explanation in another post.)

You can do what you want to do with Excel's COUNTIF function.  Do the following:

1. In the Edges worksheet, type "Event Count" into the header cell that says "Add Your Own Columns Here."

2. In the first cell below the header cell, enter this formula:  =COUNTIF([Vertex 1], Edges[[#This Row],[Vertex 1]])

That formula says "count the number of rows in the Edges table that have this person in the Vertex 1 column."  According to your description, that's the number of events this person has been to.

-- Tony

May 31, 2011 at 6:35 PM

What Merge Duplicate Edges does:

Merge edges that connect the same vertices and add an Edge Weight column to the Edges worksheet.

If the Edges worksheet contains two "A,B" rows, for example, then one of the rows is removed and the Edge Weight cell in the remaining row is set to 2.

In a directed graph, "A,B" and "B,A" are not considered duplicates.  In an undirected graph, they are considered duplicates.

This feature removes any filters that are applied to the Edges worksheet.

May 31, 2011 at 10:34 PM

Great. Thank you so much for the clarification and help