Reconstructing a table

[Ted Metro]

I can pull a table out of a repository that looks like this --

Bill Mike Sarah Jill Tom
Project 1 0 10 0 0 0
Project 2 40 0 0 12 0
Project 3 15 0 0 0 0
Project 4 0 0 5 0 15


I need to create some formulas to switch the table to look like --

Project 1 Mike 10
Project 2 Bill 40
Project 2 Jill 12
Project 3 Bill 15
Project 4 Sarah 5
Project 4 Tom 15

Basically taking the x-axis (people) and moving it over to be a nested under
projects. I can't figure out how to get there if someone could please help.

Ted

 

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = LastRow To 2 Step -1

For j = LastCol To 2 Step -1

If .Cells(i, j).Value2 <> 0 Then

.Rows(i + 1).Insert
.Cells(i, "A").Copy Cells(i + 1, "A")
.Cells(1, j).Copy .Cells(i + 1, "B")
.Cells(i, j).Copy .Cells(i + 1, "C")
End If
Next j

.Rows(i).Delete
Next i

.Rows(1).Delete
End With
End Sub

 

[Ted Metro]

It couldn't have worked better or more easily. Thank you so much Bob, and
have a great weekend!!

 

[Herbert Seidenberg]

Excel 2007 PivotTable
No code, no formulas
http://www.mediafire.com/file/u2mxmwjnwmy/03_12_10.xlsx
Pdf preview:
http://www.mediafire.com/file/4m5elewbz5i/03_12_10.pdf

 

[Bob Phillips]

No pivot either, the original cross-tab report is not pivotable, he needs to
deconstruct it if he wants to pivot it, just as he was asking.