Transposing multiple rows into columns to create Vertices

Oct 22, 2011 at 5:21 PM

I often have spreadsheets with multiple rows and columns of data that I would like to convert into graphs using NodeXL.  However, it becomes a very tedious task converting the rows into columns (using Paste Special > Transpose).  For example, I'll have a sheet where Column A are people's names.  The remain Columns will contain addresses, phone numbers, school names, etc.  I'll use Column A (people's names) as my first Vertex.  I'll then transpose the remaining columns into a single column to use as my second Vertex.  It becomes tedious because I have to go row by row, transposing them into columns associated with a single cell (name) from Column A.  Does this make sense?

Is there an easier way to do this, especially for large data sets?

Oct 23, 2011 at 6:29 PM
Edited Oct 23, 2011 at 6:30 PM

I don't understand your sequence of steps.  How do you get a second vertex out of an address, phone number, and so on?  Perhaps an example will clear things up.

If your source worksheet in a plain Excel workbook looks like this:

Name    Address    Phone Number
John    Main St.    485-382-1738
Mary    Vine St.    390-987-3929

...then what does the Edges worksheet in the NodeXL workbook end up looking like when you're done?

Vertex 1    Vertex 2
John    ????
Mary    ????

-- Tony

Oct 23, 2011 at 7:20 PM
Edited Oct 23, 2011 at 10:33 PM

You're right.  Upon re-reading my post, I guess I wasn't very clear.

Using you're example, my goal would be to create a worksheet that looks like this:

Vertex 1    Vertex 2
John Main St
John 485-382-1738
Mary Vine St.
Mary    390-987-3929

This way, I can see if John, Mary, or others share a common node, be it address, phone, email, etc.

Using Paste Special>Transpose, I can very easily convert the Address and Phone # Rows into Columns, but I'd have to do that for each row.  For large worksheets, this can become quite a chore.

Any advice?


Oct 24, 2011 at 4:17 AM

I don't know how to do that without some programming.  Fortunately, I had a program that does something similar and I modified it to do what you're doing by hand.  See "How to Create an Edge List From a List of Items, Format 2" at

-- Tony

Oct 26, 2011 at 1:29 PM


Thank you!!!!!  Your services here are truly uncanny!  I'm at a loss for words.  This will be a tremendous help.