Columns to Rows..?

G

GFP1963

New Member
Joined
Jan 12, 2018
Messages
17
Given data prepared as in the attached spreadsheet

Given a sheeet with Column A of values (say names) with corresponding columns B, C, D, etc. containing values relevant to row A,
how would you created additional rows for the names in Column A, 1 each for each value in columns B, C, D, etc.

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { color: white; font-size: 10pt; font-weight: 700; font-family: Calibri; text-align: left; background: black none repeat scroll 0% 0%; }.xl64 { font-size: 10pt; font-weight: 700; font-family: Calibri; }.xl65 { font-size: 10pt; font-weight: 700; font-family: Calibri; text-align: left; }.xl66 { font-size: 10pt; font-family: Calibri; }.xl67 { color: black; font-size: 10pt; font-family: Calibri; }.xl68 { color: white; font-size: 10pt; font-weight: 700; font-family: Calibri; background: black none repeat scroll 0% 0%; }.xl69 { color: white; font-size: 10pt; font-family: Calibri; background: black none repeat scroll 0% 0%; }</style> [TABLE="width: 432"]
<tbody>[TR]
[TD="class: xl63, width: 432, colspan: 6"]GIVEN THIS[/TD]
[/TR]
[TR]
[TD="class: xl65"]NAME[/TD]
[TD="class: xl64"]E1[/TD]
[TD="class: xl64"]E2[/TD]
[TD="class: xl64"]e3[/TD]
[TD="class: xl64"]e4[/TD]
[TD="class: xl64"]e5[/TD]
[/TR]
[TR]
[TD="class: xl66"] Sullivan[/TD]
[TD="class: xl66"]sullivan1[/TD]
[TD="class: xl66"]sullivan2[/TD]
[TD="class: xl66"]sullivan3[/TD]
[TD="class: xl66"]sullivan4[/TD]
[TD="class: xl66"]sullivan5[/TD]
[/TR]
[TR]
[TD="class: xl66"]Rodríguez[/TD]
[TD="class: xl66"]rodriguesz1[/TD]
[TD="class: xl66"]rodriguez2[/TD]
[TD="class: xl67"]rodriguez3[/TD]
[TD="class: xl67"]rodriguez4[/TD]
[TD="class: xl67"]rodriguez5[/TD]
[/TR]
[TR]
[TD="class: xl66"]Burns[/TD]
[TD="class: xl66"]burns1[/TD]
[TD="class: xl66"]burns1[/TD]
[TD="class: xl67"]burns3[/TD]
[TD="class: xl67"]burns4[/TD]
[TD="class: xl66"]burns5[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl68, colspan: 2"]END UP WITH THIS[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[TD="class: xl69"][/TD]
[/TR]
[TR]
[TD="class: xl66"] Sullivan[/TD]
[TD="class: xl66"]sullivan1[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"] Sullivan[/TD]
[TD="class: xl66"]sullivan2[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"] Sullivan[/TD]
[TD="class: xl66"]sullivan3[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"] Sullivan[/TD]
[TD="class: xl67"]sullivan4[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"] Sullivan[/TD]
[TD="class: xl66"]sullivan5[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Rodríguez[/TD]
[TD="class: xl66"]rodriguesz1[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Rodríguez[/TD]
[TD="class: xl66"]rodriguesz2[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Rodríguez[/TD]
[TD="class: xl66"]rodriguesz3[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Rodríguez[/TD]
[TD="class: xl66"]rodriguesz4[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Rodríguez[/TD]
[TD="class: xl66"]rodriguesz5[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Burns[/TD]
[TD="class: xl66"]burns1[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Burns[/TD]
[TD="class: xl66"]burns2[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Burns[/TD]
[TD="class: xl66"]burns3[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Burns[/TD]
[TD="class: xl66"]burns4[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Burns[/TD]
[TD="class: xl66"]burns5[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Thank you very much for the suggestion however, as I mentioned earlier in this thread, i am pretty much an excel novice and do not really understand how to implement the code you have posted. Is it possible you could add it to a sheet with instructions on how to use it like Virici had done for me earlier? His sample works. I "just" need to expand the number of columns it will repeat as common row data, for lack of a better explanation.
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
https://www.rondebruin.nl/win/code.htm

Your data needs to start at Cell A1 which means that "First Name" is in cell A1, "Roy" in cell A2, "Maria" in cell A3 etc etc.
"Last Name" will be in cell B1, "Company Name" in cell C1, "E1" in cell D1 etc etc
All this data can be in any sheet but the result will go to "Sheet2".
Sheet2 should therefor be empty.
You can expand your data as much as you want, Rows and/or Columns
Read Ron's explanation and you should be on your way
Run the code and let us know what needs to change or be added.
 
Upvote 0
Yeah - I don't know what I am doing. And I am using Mac version of excel. The 'for dummies' links are not really helping.
I appreciate your help. Unless you could provide me with a pre-prepared file with the code/macro in DropBox or someplace I am not going to get anywhere. I could even message you my email address, if there is a way to do that here.

I tried just replacing the code in the original file provided by Virici (post #4 ) but am getting an error.
 
Upvote 0
If you provide me with an email address via private message I'll email a file.
 
Upvote 0
2 Workbooks are in cyberspace on the way to you.
For those interested, this is the my suggestion for the example in Post #8
Code: 
Sub AAAAB()
Dim lr As Long, E1col As Long, sh2 As Worksheet, sh1 As Worksheet, lc As Long, i As Long, j As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
E1col = Cells(1, 1).EntireRow.Find("E1", , , 1, xlByColumns, xlPrevious).Column
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Application.ScreenUpdating = False
sh2.UsedRange.ClearContents
sh1.Range(Cells(1, 1), sh1.Cells(1, E1col)).Copy sh2.Range("A1")
    For i = 2 To lr
        lc = Cells(i, Columns.Count).End(xlToLeft).Column
            With sh2.Cells(Rows.Count, E1col).End(xlUp).Offset(1).Resize(lc - (E1col - 1))
                .Value = Application.Transpose(Range(Cells(i, E1col), Cells(i, lc)).Value)
                    For j = 1 To E1col - 1
                        .Offset(, -j).Value = Cells(i, E1col - j).Value
                    Next j
            End With
    Next i
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Re: I think I love you
Don't go too far now but I think I know what you mean. At least I hope so.
Glad it all worked as requested.
Good Luck
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Formula Required to Group Family Members, where ID matches
2
Replies
13
Views
2K
jtakw
jtakw
D
  • Question Question
How to output a dynamic range where the height of the range varies
Replies
0
Views
1K
dagda13
D
T
  • Question Question
Pasting Text Changes sheet font
Replies
1
Views
731
6StringJazzer
6StringJazzer
J
  • Question Question
Table values to single row for each value
Replies
5
Views
798
Fluff
Fluff
D
  • Question Question
Formula to implement a regression equation
Replies
3
Views
624
Momentman
Momentman

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top