VBA Help: Transpose data from duplicate rows to new columns

[mganzlin]

Hello! I have data that looks like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID[/TD]
[TD]Position[/TD]
[TD]School[/TD]
[TD]Endorsement[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]123
[/TD]
[TD]Teacher[/TD]
[TD]Stellar Elementary[/TD]
[TD]Endorsement 28[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]123[/TD]
[TD]Teacher[/TD]
[TD]Stellar Elementary[/TD]
[TD]Endorsement 35[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]123[/TD]
[TD]Teacher[/TD]
[TD]Stellar Elementary[/TD]
[TD]Endorsement 12[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]124[/TD]
[TD]Custodian[/TD]
[TD]Thunder Middle[/TD]
[TD]Endorsement 25[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]124[/TD]
[TD]Custodian[/TD]
[TD]Thunder Middle[/TD]
[TD]Endorsement 1[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]125[/TD]
[TD]Teacher Elem[/TD]
[TD]Lightning Elementary[/TD]
[TD]Endorsement 15[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]126[/TD]
[TD]Teacher[/TD]
[TD]North High School[/TD]
[TD]Endorsement 28[/TD]
[/TR]
</tbody>[/TABLE]

And I want it to look like this:
[TABLE="width: 650"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID[/TD]
[TD]Position[/TD]
[TD]Endorsement 1[/TD]
[TD]Endorsement 2[/TD]
[TD]Endorsement 3[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]123[/TD]
[TD]Teacher[/TD]
[TD]Endorsement 28[/TD]
[TD]Endorsement 35[/TD]
[TD]Endorsement 12[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]124[/TD]
[TD]Custodian[/TD]
[TD]Endorsement 25[/TD]
[TD]Endorsement 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]125[/TD]
[TD]Teacher Elem[/TD]
[TD]Endorsement 15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]126[/TD]
[TD]Teacher[/TD]
[TD]Endorsement 28[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So there are no duplicate rows anymore. I need to keep the first 4 columns.

 

[Joe4]

Welcome to the Board!

I just helped someone with a similar question just a few minutes ago. I minor adaption to that VBA should work for you, as long as your data is sorted like that so that records being grouped together are always lumped together.

Try this:

Sub MyCombineRows()


    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Set first row to start on (skipping first row of data)
    r = 3
    
    Do
'       Check to see if columns A, B, C, and D are equal to row above it
        If Cells(r, "A") = Cells(r - 1, "A") And Cells(r, "B") = Cells(r - 1, "B") And _
            Cells(r, "C") = Cells(r - 1, "C") And Cells(r, "D") = Cells(r - 1, "D") Then
'           Copy value from column E to end of row above it
            Cells(r - 1, Columns.Count).End(xlToLeft).Offset(0, 1) = Cells(r, "E")
'           Delete row
            Rows(r).Delete
        Else
'           Move on to next row
            r = r + 1
        End If
    Loop Until Cells(r, "A") = ""
    
    Application.ScreenUpdating = True
    
End Sub

 

[mganzlin]

Hi Joe! Thank you for responding! When I click 'Run', nothing happens??

 

[mganzlin]

Ah, removed comments and it worked great. I'm literally crying happy tears. Thank you thank you thank you.

 

[Joe4]

You are welcome!

BTW, the comments shouldn't be an issue, as long as you left the single-quotes before them.

 

[egandb]

Try this:

Thank you Joe4! Had a similar spreadsheet with multiple rows I needed to transpose to columns. Your comments on the VBA code really helped me to understand what I needed to do to modify it to get it done. THANK YOU SO MUCH!!

 

[Joe4]

Thank you Joe4! Had a similar spreadsheet with multiple rows I needed to transpose to columns. Your comments on the VBA code really helped me to understand what I needed to do to modify it to get it done. THANK YOU SO MUCH!!

You are welcome.
I am glad that the old thread helped you out!
And welcome to the Board!

 

[MattGS]

Hi is it possible to also use this VBA code for multiple columns? where the code lists E to move data, I have 4 columns to move data up and to the end, is this possible?

 

[Joe4]

Hi is it possible to also use this VBA code for multiple columns? where the code lists E to move data, I have 4 columns to move data up and to the end, is this possible?

It should be possible, but I think it would be most helpful if you post a sample of your data and expected results, like the original poster did, so we can get a clear picture of exactly how your data is structured and your desired results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also, while your question is somewhat similar to this, it is distinctly different. As such, it would probably be best to post it to a new thread (instead of posting it on to an old thread). That way, it will appear on the "Unanswered threads" listing for all to easily see, and will probably get more attention. You can always provide a link back to this thread in your new question, if you see a benefit in doing so.