Reconstructing a table

T

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
 
B

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
 
T

Ted Metro

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top