ExcelBanter - Reconstructing a table

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Reconstructing a table (https://www.excelbanter.com/excel-worksheet-functions/258779-reconstructing-table.html)

Reconstructing a table

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

Reconstructing a table

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

--

HTH

Bob

"Ted Metro" wrote in message
...
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

Reconstructing a table

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

"Bob Phillips" wrote:

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

--

HTH

Bob

"Ted Metro" wrote in message
...
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

.

Reconstructing a table

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.

--

HTH

Bob

"Herbert Seidenberg" wrote in message
...
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