Using Excel to move rows to columns on repeating rows?

Question

I have an Excel sheet like the following, in 2 columns:

Tom   Alaska
Tom   Hawaii
Tom   New York
Tom   California
Bob   North Carolina
Bob   New York
Bob   Maine
Bob   Alaska
Bob   Wyoming

I need this in the following format:

Tom   Alaska   Hawaii   New York   California
Bob   North Carolina   New York   Maine   Alaska   Wyoming

I've searched the web and this site, but I honestly don't know how to pose the question. Sorry if this seems simple, I've tried pivot tables, but can't get it in the format I described above. I've searched for pivot rows to columns, but that just gets me tips on transposing rows and columns and that's not what I need.

Any help would be appreciated.

Thanks, TomHere's an example of what I'm looking to do in Excel

Is the no. of rows fixed for each name in 1st column? Do you want this for 1 time job or will you be doing this again and again? If 1 time and fixed rows then you can do some dirty work with formula and get it. If you require it to run again and again or the no of rows are not fixed, VBA macro would be best suited for this. — Techie, Apr 20 '16 at 19:38
The number of rows aren't fixed for each name in the first column. So Tom can be 1 row and Bob can be 6 rows.I realized this after I posted my example, as that would be important to know. Apologies for not being clearer. — TMathew, Apr 21 '16 at 16:01

JNevill · Answer 1 · 2016-04-22T13:26:24.127

Here's a VBA subroutine that will do this for you. I commented the snot out of it, so you can make sense of it and change things as needed:

Sub transpoooooooooOOOOooose()
    Dim rngCell As Range 'variable to hold which cell we are on
    Dim intCol As Integer 'variable to hold the column we are writing to
    Dim intRow As Integer 'variable to hold the row we are writing to.
    Dim strGuysName As String 'variable to hold the name of the dude

    'first we will write to column 2 (column 1 will contain the name of the guy)
    intCol = 2

    'and row 0 (it will be popped up to row 1 in the first iteration
    intRow = 0

    'Those will be increased as we iterate below

    'iterate through every filled out cell in column A
    For Each rngCell In Sheet1.Range("A:A")
        If rngCell.Value = "" Then Exit Sub 'we hit the bottom of the list, exit

        'If this is a new dude, then increase intRow
        '  and capture the name
        '  and send intCol back to 2
        If rngCell.Value <> strGuysName Then
            intRow = intRow + 1 'see, we popped it up one.
            strGuysName = rngCell.Value
            intCol = 2
        End If

        'Now... the meat and potatoes
        Sheet2.Cells(intRow, 1).Value = strGuysName 'write out the guys name
        Sheet2.Cells(intRow, intCol).Value = rngCell.Offset(, 1).Value 'write out the city which is one column over

        'increase the intCol by 1
        intCol = intCol + 1

    Next
End Sub

You could also do this with front end excel stuff.

First, get a unique list of dude's names from col A and stick those in a new sheet (sheet2)

Then, in Sheet2!B1 (next to Tom) use:

=IF(INDEX(Sheet1!$A:$A, MATCH($A1,Sheet1!$A:$A, 0) + COLUMN() - 2)=$A1, INDEX(Sheet1!$B:$B, MATCH($A1,Sheet1!$A:$A, 0) + COLUMN() - 2), "")

That beast will find "Tom" in Sheet1 (your original list) and return the row that he is found. It will then go down the number of rows that are equal to the column this formula is in minus 2 (since we start in B). So column B version checks the first row "Tom" is found, then Column C version finds one row after that, and Column D finds one row after that. If that cell whatever number of rows down is "Tom" still, then do the same logic, but grab whatever is in B.

score 0 · Answer 2 · edited May 23 '17 at 11:59

Thanks so much everyone, I'm going to try out each of your suggestions. I continued looking through this site, and I did find a similar problem someone was having, and they used Excel formulas to do it:

Combining duplicate entries with unique data in Excel

Basically a third column would consolidate the values in column B into comma separated values, and a fourth column would be used to uniquely identify the last row among the duplicates in column A, and then I guess I can filter that out and then use comma separators to split out the third column into individual columns.

I think this solution will work, but I will try these other suggestions as well. Again, I appreciate this!

Thanks, Tom

Using Excel to move rows to columns on repeating rows?

2 Answers2