How to rearrange data
 

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!

How to rearrange data
 

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!

How to rearrange data
 

Cecil, thank you for the macro. I will try it out.

In case an error has been made and someone has put the names of two
mayors for the same municipality and year, is there a way to add
something to the macro so that it flags this. Maybe, it could add
additional lines for the country and put at the country and year
intersections all the names found. It would be good also if the names
of the mayors were turned red when there is more than one for a country
and year.

It would be really good if this could be put in the macro also. Once
again, thank you.

Michel

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

How to rearrange data
 

Michel,
Hope I am not too late,
here is the revised macro which will highlight the row if
any municipality have two or more mayors in the same year
Regards,
Cecil

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("A1:C" & LRow).Interior.ColorIndex = -4142
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
If IsEmpty(Cells(x, y)) Then
Cells(x, y).Value = Range("C" & i).Value
Else
Range("A" & i & ":C" & i).Interior.ColorIndex = 3
End If
Next i
End Sub

"Michel Rousseau" wrote in message
...

Cecil, thank you for the macro. I will try it out.

In case an error has been made and someone has put the names of two
mayors for the same municipality and year, is there a way to add
something to the macro so that it flags this. Maybe, it could add
additional lines for the country and put at the country and year
intersections all the names found. It would be good also if the names
of the mayors were turned red when there is more than one for a country
and year.

It would be really good if this could be put in the macro also. Once
again, thank you.

Michel

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

How to rearrange data
 

Cecil,

A big thank you! The programme was very good! I had the idea to build
on this table from the macro – would this be possible?

For each intersection of municipality and year in the table, I made the
following unit:

Municipality 1940
Seville name mayor
Main issue
Year

Each unit has three cells going down. In addition to the “name”, there
is “main issue” and “year”.
There is a unit for each municipality for all of the years going across.

The same has been done for 4 municipalities, so there is a worksheet
with this fixed structu

Municipality 1940 1950 1960 1970 1980 1990 2000

Seville name mayor etc
Main issue
Alternative year
Grenada name mayor etc
Main issue
Alternative year
Barcelona name mayor etc
Main issue
Alternative year
Cordoba name mayor etc
Main issue
Alternative Year

The table is in one worksheet, and there is one worksheet for each of
the municipalities afterwards.

Is is possible for a macro to read the unit for each municipality for
each year going across (name mayor, main issue, alternative year) and
then to go the individual municipality worksheet, to find the row which
has all the same information in the unit (name of mayor, main issue,
year). If this info is the same, extract the first five columns of this
row in the municipality worksheet and put it in a separate worksheet.

And to do this for each unit at the intersection of a municipality and
year in the table worksheet.

I hope this is not too complicated. Let me know what you think.

Michel







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

How to rearrange data
 

Michel,
send a sample workbook.

regards,
Cecil

"Michel Rousseau" wrote in message
...
Cecil,

A big thank you! The programme was very good! I had the idea to build
on this table from the macro - would this be possible?

For each intersection of municipality and year in the table, I made the
following unit:

Municipality 1940
Seville name mayor
Main issue
Year

Each unit has three cells going down. In addition to the "name", there
is "main issue" and "year".
There is a unit for each municipality for all of the years going across.

The same has been done for 4 municipalities, so there is a worksheet
with this fixed structu

Municipality 1940 1950 1960 1970 1980 1990 2000

Seville name mayor etc
Main issue
Alternative year
Grenada name mayor etc
Main issue
Alternative year
Barcelona name mayor etc
Main issue
Alternative year
Cordoba name mayor etc
Main issue
Alternative Year

The table is in one worksheet, and there is one worksheet for each of
the municipalities afterwards.

Is is possible for a macro to read the unit for each municipality for
each year going across (name mayor, main issue, alternative year) and
then to go the individual municipality worksheet, to find the row which
has all the same information in the unit (name of mayor, main issue,
year). If this info is the same, extract the first five columns of this
row in the municipality worksheet and put it in a separate worksheet.

And to do this for each unit at the intersection of a municipality and
year in the table worksheet.

I hope this is not too complicated. Let me know what you think.

Michel







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