How to rearrange data

[Michel Rousseau]

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

Re: 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

 

[Cecilkumara Fernando]

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

 

[Cecilkumara Fernando]

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]

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 structure:

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

 

[Cecilkumara Fernando]

Michel,
send a sample workbook.
(e-mail address removed)
regards,
Cecil