Michel,
Excel being a flat database ( row & column ), it is difficult to do
manipulation to a table with two variables in the same row.
Any way the following macro will do the job
Assuming that any municipality cant have two mayors in the same year.
copy the table to a new workbook,
headers should be in first row from A1 to C1
and run this macro
HTH
Cecil

Sub ReArrangetb()
Dim LRow As Double
Dim LRowYear As Double
Dim LRowMcity As Double
Dim i As Double
Dim x As Double
Dim y As Double
Dim McityRng As Range
Dim YearRng As Range

LRow = Range("A" & Rows.Count).End(xlUp).Row
Range("E1:F1").EntireColumn.Clear
Range("A1:A" & LRow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("E1"), Unique:=True
Range("B1:B" & LRow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("F2"), Unique:=True
LRowMcity = Range("E" & Rows.Count).End(xlUp).Row
LRowYear = Range("F" & Rows.Count).End(xlUp).Row
Range("F2:F" & LRowYear).Sort Key1:=Range("F3"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("F3:F" & LRowYear).Copy
With Range("F1")
..PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
End With
Application.CutCopyMode = False
Range(Cells(2, 6), Cells(LRowYear, LRowYear + 4)).Clear
Set McityRng = Range("E1:E" & LRowMcity)
Set YearRng = Range(Cells(1, 5), Cells(1, LRowYear + 3))
For i = 2 To LRow
x = Evaluate("Match(" & Range("A" & i).Address & "," & _
McityRng.Address & ", 0)")
y = Evaluate("Match(" & Range("B" & i).Address & "," & _
YearRng.Address & ", 0)") + 4
Cells(x, y).Value = Range("C" & i).Value
Next i
End Sub

"Michel Rousseau" wrote in message
...
The only problem is that what is inside the table are names, not
numbers. I do not think names can be put inside a pivot table. Is
there a solution?

Michel

how to rearrange data
From: Alex J
Date Posted: 9/18/2004 4:37:00 PM

Michel,
Your best bet would be a Pivot table

AlexJ

"Michel Rousseau" wrote in message
...

How can one rearrange the following yearly data into a summary table
form with years going across? It is in an excel worksheet

example: names of mayors

municipality year name
xxx 1901 zzz
etc. etc. etc.

I would like the new table to have the municipality running down the
left side, the years running across and the names of the mayors inside
the table.

Thank you.

Michel





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!