Switch over Macro - Help

Olearus · Feb 3, 2013

Dear friends,

I have adata base. In this data base the main comluns are hhcode, village, ward andproperty. The property column can take tree values (1.00, 2.00 and 3.00) and I havethree different necessities depending on the case:<o

></o

>
<o

> </o

>
1) Whenhhcode, village and ward are the same between two rows (for example in row 4and 5 these columns take the following values: hhcode=1.00, village= 12, ward=3) and property has the 2.00 value in the row 5, I will need that all the row 5up to row 4 following the data which are contented in this row, so from Mcolumn<o

></o

>
2) When hhcode, village and ward are the samebetween three rows (for example in row 6, 7 and 8 columns take the followingvalues: hhcode=1.00, village= 7, ward= 6) and property takes the 2.00 value inthe row 7 and the 3.00 value in row 8, I will need that all the data in the row7 up to row 6 following the data which are in this row, so from M column and allthe data in the row 8 up to row 6 following the data which are in this row sofrom Y column<o

></o

>
3) When hhcode, village and ward are the samebetween two rows (for example in row 9 and 10 columns take the followingvalues: hhcode=1.00, village= 8, ward= 5) and property takes the 3.00 value inthe row 10, I will need that all the row 10 up to row 9 from Y column

Thank you very much for your help.

Olearus

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	hhcode	village	ward	Property	Home	Pond	Garden	CultivOne	CultivTwo	CultivThr	CultivIrri	Uncultiv	hhcode	village	ward	Property	Home	Pond	Garden	CultivOne	CultivTwo	CultivThr	CultivIrri	Uncultiv	hhcode	village
2	0001	11	3	1.00	30.00	50.00	12.00	56.00	56.00	0.00	0.00	0.00	0001	11	3	2.00	0.00	0.00	0.00	0.00	0.00	###	###	0.00
3
4	0001	12	2	1.00	32.00	24.00	9.00	0.00	0.00	90.00	90.00	0.00
5	0001	12	2	2.00	0.00	0.00	0.00	0.00	0.00	#####	####	0.00
6	0001	7	6	1.00	10.00	5.00	0.00	0.00	0.00	18.00	18.00	0.00
7	0001	7	6	2.00	0.00	0.00	0.00	0.00	0.00	24.00	24.00	0.00
8	0001	7	6	3.00	0.00	0.00	0.00	0.00	0.00	#####	####	0.00
9	0001	8	5	1.00	18.00	0.00	4.00	0.00	0.00	0.00	0.00	0.00
10	0001	8	5	3.00	0.00	0.00	0.00	0.00	20.00	54.00	54.00	0.00
11	0001	9	4	1.00	18.00	0.00	0.00	0.00	0.00	40.00	40.00	0.00
12	0002	1	9	1.00	8.00	75.00	0.00	0.00	0.00	#####	####	0.00
13	0002	11	3	1.00	30.00	5.00	18.00	0.00	0.00	#####	####	0.00
14	0002	11	3	3.00	0.00	0.00	0.00	0.00	0.00	#####	####	0.00
15	0002	12	2	1.00	32.00	24.00	9.00	0.00	0.00	75.00	75.00	0.00
16	0002	12	2	2.00	0.00	0.00	0.00	0.00	0.00	60.00	60.00	0.00
17	0002	12	2	3.00	0.00	0.00	0.00	0.00	0.00	75.00	75.00	0.00
18	0002	13	2	1.00	21.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
19	0002	13	2	2.00	0.00	0.00	0.00	0.00	0.00	48.00	48.00	0.00

hiker95 · Feb 3, 2013

Olearus,

Sample raw data in worksheet Sheet1:

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

1

hhcode

village

ward

Property

Home

Pond

Garden

CultivOne

CultivTwo

CultivThr

CultivIrri

Uncultiv

2

1

11

3

1

30

50

12

56

0

3

1

11

3

2

0

###

0

4

1

12

2

1

32

24

9

0

90

0

5

1

12

2

0

#####

####

0

6

1

7

6

1

10

5

0

18

0

7

1

7

6

2

0

24

0

8

1

7

6

3

0

#####

####

0

9

1

8

5

1

18

0

4

0

10

1

8

5

3

0

20

54

0

11

1

9

4

1

18

0

40

0

12

2

1

9

1

8

75

0

#####

####

0

13

2

11

3

1

30

5

18

0

#####

####

0

14

2

11

3

0

#####

####

0

15

2

12

2

1

32

24

9

0

75

0

16

2

12

2

0

60

0

17

2

12

2

3

0

75

0

18

2

13

2

1

21

0

19

2

13

2

0

48

0

20

Sheet1

After the macro in a new worksheet Results:

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

hhcode

village

ward

Property

Home

Pond

Garden

CultivOne

CultivTwo

CultivThr

CultivIrri

Uncultiv

hhcode

village

ward

Property

Home

2

1

11

3

1

30

50

12

56

0

1

11

3

2

0

3

1

12

2

1

32

24

9

0

90

0

1

12

2

0

4

1

7

6

1

10

5

0

18

0

1

7

6

2

0

5

1

8

5

1

18

0

4

0

1

8

5

3

0

6

1

9

4

1

18

0

40

0

7

2

1

9

1

8

75

0

#####

####

0

8

2

11

3

1

30

5

18

0

#####

####

0

2

11

3

0

9

2

12

2

1

32

24

9

0

75

0

2

12

2

0

10

2

13

2

1

21

0

2

13

2

0

11

Results

1. What is the worksheet name for the raw data?

2. What is the worksheet name for the results?

3. Is the raw data already sorted/grouped by columns A, B, C?

hiker95 · Feb 4, 2013

Olearus,

Sample raw data in worksheet Sheet1:

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

1

hhcode

village

ward

Property

Home

Pond

Garden

CultivOne

CultivTwo

CultivThr

CultivIrri

Uncultiv

2

1

11

3

1

30

50

12

56

0

3

1

11

3

2

0

###

0

4

1

12

2

1

32

24

9

0

90

0

5

1

12

2

0

#####

####

0

6

1

7

6

1

10

5

0

18

0

7

1

7

6

2

0

24

0

8

1

7

6

3

0

#####

####

0

9

1

8

5

1

18

0

4

0

10

1

8

5

3

0

20

54

0

11

1

9

4

1

18

0

40

0

12

2

1

9

1

8

75

0

#####

####

0

13

2

11

3

1

30

5

18

0

#####

####

0

14

2

11

3

0

#####

####

0

15

2

12

2

1

32

24

9

0

75

0

16

2

12

2

0

60

0

17

2

12

2

3

0

75

0

18

2

13

2

1

21

0

19

2

13

2

0

48

0

20

Sheet1

After the macro in a new worksheet Results:

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

1

hhcode

village

ward

Property

Home

Pond

Garden

CultivOne

CultivTwo

CultivThr

CultivIrri

Uncultiv

2

1

11

3

1

30

50

12

56

0

3

1

12

2

1

32

24

9

0

90

0

4

1

7

6

1

10

5

0

18

0

5

1

8

5

1

18

0

4

0

6

1

9

4

1

18

0

40

0

7

2

1

9

1

8

75

0

#####

####

0

8

2

11

3

1

30

5

18

0

#####

####

0

9

2

12

2

1

32

24

9

0

75

0

10

2

13

2

1

21

0

11

Results

Excel Workbook

M

N

O

P

Q

R

S

T

U

V

W

X

1

hhcode

village

ward

Property

Home

Pond

Garden

CultivOne

CultivTwo

CultivThr

CultivIrri

Uncultiv

2

1

11

3

2

0

###

0

3

1

12

2

0

#####

####

0

4

1

7

6

2

0

24

0

5

1

8

5

3

0

20

54

0

6

7

8

2

11

3

0

#####

####

0

9

2

12

2

0

60

0

10

2

13

2

0

48

0

11

Results

Excel Workbook

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

1

hhcode

village

ward

Property

Home

Pond

Garden

CultivOne

CultivTwo

CultivThr

CultivIrri

Uncultiv

2

3

4

1

7

6

3

0

#####

####

0

5

6

7

8

9

2

12

2

3

0

75

0

10

11

Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Option Explicit
Sub ReorgData()
' hiker95, 02/04/2013
' http://www.mrexcel.com/forum/excel-questions/683313-switch-over-macro-help.html
Dim w1 As Worksheet, wR As Worksheet
Dim r As Long, rr As Long, lr As Long, lr2 As Long, nr As Long, c As Long, nc As Long, n As Long, i As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
lr = w1.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
w1.Range("M1") = "M"
With w1.Range("M2:M" & lr)
  .FormulaR1C1 = "=RC[-12]&RC[-11]&RC[-10]"
  .Value = .Value
End With
w1.Columns(13).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=w1.Columns(14), Unique:=True
lr2 = w1.Cells(Rows.Count, 14).End(xlUp).Row
With w1.Range("O2:O" & lr2)
  .Formula = "=COUNTIF($M:$M,$N2)"
  .Value = .Value
End With
n = Application.Max(w1.Range("O2:O" & lr2))
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
c = 1
For i = 1 To n
  wR.Cells(1, c).Resize(, 12).Value = w1.Cells(1, 1).Resize(, 12).Value
  c = c + 12
Next i
For r = 2 To lr
  n = Application.CountIf(w1.Columns(13), w1.Cells(r, 13).Value)
  nr = wR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  nc = 1
  rr = r
  For i = 1 To n Step 1
    w1.Cells(rr, 1).Resize(, 12).Copy wR.Cells(nr, nc)
    rr = rr + 1
    nc = nc + 12
  Next i
  r = r + n - 1
Next r
w1.Range("M1:O" & lr).ClearContents
wR.Cells.EntireColumn.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

hiker95 · Feb 4, 2013

Olearus,

I forgot to say, that the macro will work correctly only if your data is grouped/sorted by your example in columns A, B, and C.

Switch over Macro - Help

Olearus

New Member

Excel Facts

hiker95

Well-known Member

hiker95

Well-known Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

Switch over Macro - Help

Olearus

New Member

Excel Facts

hiker95

Well-known Member

hiker95

Well-known Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock