Reformat of a table

[mralmackay]

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 ([email protected])

 

[Dave Peterson]

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

 

[Herbert Seidenberg]

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