Reconstructing a table
in [Functions]
|
Prev: colating multi rows of data into single rows - no to pivot tab
Next: How do I lock ONLY the formatting on an excel sheet?
From: Ted Metro on 12 Mar 2010 11:12 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
From: Bob Phillips on 12 Mar 2010 12:20 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" <TedMetro(a)discussions.microsoft.com> wrote in message news:A0D0D7CD-EE26-4BB9-98BA-68FB43E640B5(a)microsoft.com... >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 > > >
From: Ted Metro on 12 Mar 2010 15:16 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" <TedMetro(a)discussions.microsoft.com> wrote in message > news:A0D0D7CD-EE26-4BB9-98BA-68FB43E640B5(a)microsoft.com... > >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 > > > > > > > > > . >
From: Herbert Seidenberg on 12 Mar 2010 15:32 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
From: Bob Phillips on 12 Mar 2010 19:27
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" <herbds7-msxls(a)yahoo.com> wrote in message news:82347adb-72b8-494c-b0e9-edc57d830adf(a)u19g2000prh.googlegroups.com... > 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 |