Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformat of a table
Is it possible to display table in a different way by perhaps using a
pivot table or a macro? I have data like this: Column = Code Row @ top = Quarter Data = Name Some example data appears below: Code Q4 2006 Q1 2007 Q2 2007 CU1 User 1 User 1 User 1 CU2 User 2 User 2 User 2 CU3 User 3 User 3 User 4 CU4 User 5 User 5 User 6 CU5 User 7 User 7 User 7 The way I need the data to show is: Q4 2006 Q1 2007 Q2 2007 User 1 CU1 CU1 CU1 User 2 CU2 CU2 CU2 User 3 CU3 CU3 User 4 CU4 User 5 CU4 CU4 User 6 CU4 User 7 CU5 CU5 CU5 Appreciate your help in this!. Thanks, Al ) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformat of a table
Shouldn't that entry under "Q2 2007" in the "User 4" be CU3, not CU4?
If yes, then this seemed to work ok for me: Option Explicit Sub testme01() Dim CurWks As Worksheet Dim NewWks As Worksheet Dim iCol As Long Dim iRow As Long Dim DestCell As Range Dim RowMatch As Variant Set CurWks = Worksheets("sheet1") Set NewWks = Worksheets.Add NewWks.Range("a1").Value = "Name" With CurWks For iCol = 2 To .Cells(1, .Columns.Count).End(xlToLeft).Column With NewWks Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With .Range(.Cells(2, iCol), .Cells(.Rows.Count, iCol).End(xlUp)).Copy _ Destination:=DestCell Next iCol End With With NewWks With .Range("a:a") .Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes .AdvancedFilter action:=xlFilterCopy, copytorange:=.Range("b1"), _ unique:=True .Delete End With End With With CurWks .Range(.Cells(1, "B"), .Cells(1, .Columns.Count).End(xlToLeft)).Copy _ Destination:=NewWks.Range("b1") For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row For iCol = 2 To .Cells(iRow, .Columns.Count).End(xlToLeft).Column If .Cells(iRow, iCol).Value = "" Then 'do nothing, empty cell Else RowMatch = Application.Match(.Cells(iRow, iCol).Value, _ NewWks.Columns(1), 0) 'put it in the same column If IsError(RowMatch) Then 'shouldn't happen MsgBox "Error with: " _ & .Cells(iRow, iCol).Address(0, 0) Else NewWks.Cells(RowMatch, iCol).Value _ = .Cells(iRow, "A").Value End If End If Next iCol Next iRow End With End Sub wrote: Is it possible to display table in a different way by perhaps using a pivot table or a macro? I have data like this: Column = Code Row @ top = Quarter Data = Name Some example data appears below: Code Q4 2006 Q1 2007 Q2 2007 CU1 User 1 User 1 User 1 CU2 User 2 User 2 User 2 CU3 User 3 User 3 User 4 CU4 User 5 User 5 User 6 CU5 User 7 User 7 User 7 The way I need the data to show is: Q4 2006 Q1 2007 Q2 2007 User 1 CU1 CU1 CU1 User 2 CU2 CU2 CU2 User 3 CU3 CU3 User 4 CU4 User 5 CU4 CU4 User 6 CU4 User 7 CU5 CU5 CU5 Appreciate your help in this!. Thanks, Al ) -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reformat of a table
You can use Pivot Table if you trim the Code to a numerical value.
Use Multiple Consolidation Ranges. Layout: ROW = Value COLUMN = Column DATA = Sum of Row Options: Uncheck Grand Totals. Sum of Row Column Value Q4 06 Q1 07 Q2 07 User1 1 1 1 User2 2 2 2 User3 3 3 User4 3 User5 4 4 User6 4 User7 5 5 5 If you cannot trim Code, use VLOOKUP to translate the PT |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a Word Table from a Excel Macro | Excel Discussion (Misc queries) | |||
How do I link a cell outside a pivot table to one inside the table | Excel Discussion (Misc queries) | |||
Custom field in Pivot Table? | Excel Discussion (Misc queries) | |||
Pivot Table external XLS file source change and GETPIVOTDATA refresh | Excel Discussion (Misc queries) | |||
Change Data In Pivot Table | New Users to Excel |