advanced transpose

[Michaelbbeck]

H guys, great forum been checking in a while now, so thank you in advance. I am by no means an expert but can do a few intermediate things.

I have a question around grouped transpose - or thats what i have called it. I have a list of customers that have been collected without any thought of data.

The data is stacked in two columns as such. I can transpose but it doesn't work. Her is the format

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]aa[/TD]
[/TR]
[TR]
[TD]Location:[/TD]
[TD]bb[/TD]
[/TR]
[TR]
[TD]Phone:[/TD]
[TD]cc[/TD]
[/TR]
[TR]
[TD]Fuel:[/TD]
[TD]dd[/TD]
[/TR]
[TR]
[TD]Water:[/TD]
[TD]ee[/TD]
[/TR]
[TR]
[TD]Power:[/TD]
[TD]ff[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]uu[/TD]
[/TR]
[TR]
[TD]Location:[/TD]
[TD]vv[/TD]
[/TR]
[TR]
[TD]Phone:[/TD]
[TD]ww[/TD]
[/TR]
[TR]
[TD]Fuel:[/TD]
[TD]xx[/TD]
[/TR]
[TR]
[TD]Water:[/TD]
[TD]yy[/TD]
[/TR]
[TR]
[TD]Power:[/TD]
[TD]zz[/TD]
[/TR]
</tbody>[/TABLE]


You will see there are recurring headings. Id like the a column to be the header, then the B column underneath. I have about 1000 records is there an automated way to do this?

Once again thank you so much in advance.

Michael

 

[Yongle]

Use 2 sheets

In Sheet1
Paste your example data in cells A1 to B12


In sheet2
Formula in A1 copied across as far as E1
=OFFSET(Sheet1!$A1,COLUMN()-1,0)

Formula in A2 copied down and across
=OFFSET(Sheet1!$B1,(ROW()-2)*5+(COLUMN()-1),0)

 

[Fluff]

With VBA

Sub Transp()
   Dim Rng As Range
   Dim Hdr As Range
   With Range("A:A")
      .Replace "Name", "=xxx", xlWhole, , False, , False, False
      Set Hdr = .SpecialCells(xlConstants).Areas(1)
      Range("C1").Value = "Name"
      Range("D1").Resize(, Hdr.Count).Value = Application.Transpose(Hdr)
      For Each Rng In .SpecialCells(xlConstants).Areas
         Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(, Rng.Count + 1).Value = Application.Transpose(Rng.Offset(-1, 1).Resize(Rng.Count + 1))
      Next Rng
      .Replace "=xxx", "Name", xlWhole, , False, , False, False
   End With
End Sub

 

[Michaelbbeck]

thanks, works a treat!!!

 

[Fluff]

Not sure which of us you're talking too, but glad we could help & thanks for the feedback