I would think that you would really want:

Name, PropertyTitle, Qty/Number/whatever

If you don't want that second column just delete it after you run this macro:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCol As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim oRow As Long
Dim oCol As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 3).Value _
= Array("Name", "Property", "Value")

oRow = 1
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 2

For iRow = FirstRow To LastRow
LastCol = .Cells(iRow, .Columns.Count).End(xlToLeft).Column
For iCol = FirstCol To LastCol
If Trim(.Cells(iRow, iCol)) = "" Then
'do nothing
Else
oRow = oRow + 1
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, iCol).Value
End If
Next iCol
Next iRow
End With

NewWks.UsedRange.Columns.AutoFit

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


sjn wrote:

I have some data that i want to layout in a different format. At present it
is as follows:

Name, Property1, Property2, ... Property172
abc, 786 7684 8965
abd 645 64573 64328
... (there are 2007 rows)

although there are 172 columns there may not necessarily be an entry in
every column for each name.

I want it to be in the format
Name, Property
abc, 786
abc, 7684
abc, 8965
abd, 645
abd, 64573
abd, 64328

Can anybody help?

Thanks ;o)

--

Dave Peterson