If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
Reconstructing a table
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 |
#5
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|