Convert columns into rows with a repeating attribute

[shaunriordan]

Hello,

I am trying to turn the following row (and similar rows);

Name Age Gender Tel Number
John Smith 32 Male 09876 345432

into a number of multiple rows;

Name Attribute Name Attribute Value
John Smith Age 32
John Smith Gender Male
John Smith Tel Number 09876345432

Any help much appreciated.

Regards
Shaun

 

[venkat1926]

for testing I have added one more name. the main data will be like this

Excel Workbook
	A	B	C	D
1	Name	Age	Gender	Tel Number
2	John Smith	32	Male	09876 345432
3	guy thomas	34	male	09875 456456
Sheet1

try this macro

Sub test()
Dim j As Long, r As Range, c As Range, dest As Range
Set r = Range(Range("A2"), Cells(Rows.Count, "A").End(xlUp))
For Each c In r
For j = 1 To 3
Set dest = Cells(Rows.Count, "g").End(xlUp).Offset(1, 0)
dest = c
dest.Offset(0, 1) = c.Offset(0, j)
Next j
Next c
Columns("G:H").AutoFit
Range("G2").CurrentRegion.Cut Range("G1")
End Sub

Sub undo()
Range(Range("G1"), Cells(1, Columns.Count)).EntireColumn.Delete
End Sub

result will be from J1 same sheet

Excel Workbook
	G	H
1	John Smith	32
2	John Smith	Male
3	John Smith	09876 345432
4	guy thomas	34
5	guy thomas	male
6	guy thomas	09875 456456
Sheet1

 

[Peter_SSs]

Shaun

Welcome to the MrExcel board!

Here's another code to test in a copy of your workbook.
It assumes data starts in cell A1 and should handle any number of attributes that are listed across row 1 from columns B, C, D, ...

If you happen to have a large set of data, it should still be pretty quick.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Rearrange_Attributes()<br>    <SPAN style="color:#00007F">Dim</SPAN> a, b<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, attribs <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    rws = Range("A" & Rows.Count).End(xlUp).Row<br>    attribs = Cells(1, Columns.Count).End(xlToLeft).Column - 1<br>    <SPAN style="color:#00007F">ReDim</SPAN> b(1 <SPAN style="color:#00007F">To</SPAN> rws * attribs + 1, 1 <SPAN style="color:#00007F">To</SPAN> 3)<br>    a = Range("A1").Resize(rws, attribs + 1).Value<br>    <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> rws<br>        <SPAN style="color:#00007F">For</SPAN> j = 1 <SPAN style="color:#00007F">To</SPAN> attribs<br>            k = (i - 2) * attribs + j + 1<br>            b(k, 1) = a(i, 1)<br>            b(k, 2) = a(1, j + 1)<br>            b(k, 3) = a(i, j + 1)<br>        <SPAN style="color:#00007F">Next</SPAN> j<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">With</SPAN> Cells(1, attribs + 3).Resize(rws * attribs + 1, 3)<br>        .Value = b<br>        .Resize(1).Value = _<br>            Array("Name", "Attribute Name", "Attribute Value")<br>        .EntireColumn.AutoFit<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>