Traspose each row every 5 rows (of 15 cells) into one column

Nataly92 · Oct 19, 2013

I am using Microsoft Office Excel 2007 and Windows 7.
I have a lot of data (1-4 thousands of rows) where each column represents a year for 15 years. Every set of 5 rows are different data for a company and the values for every year of each data are under the representative column.

I want to transpose the values for every year, of one data at a time, into one column. Then do the same for the next data.

For Example I have something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1998[/TD]
[TD]1999[/TD]
[TD]2000[/TD]
[TD]2001[/TD]
[TD]2002[/TD]
[TD]2003[/TD]
[TD]2004[/TD]
[TD]2005[/TD]
[TD]2006[/TD]
[TD]2007[/TD]
[TD]2008[/TD]
[TD]2009[/TD]
[TD]2010[/TD]
[TD]2011[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]comp.1data1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]comp.1data2[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]comp.1data3[/TD]
[TD]31[/TD]
[TD]32[/TD]
[TD]33[/TD]
[TD]34[/TD]
[TD]35[/TD]
[TD]36[/TD]
[TD]37[/TD]
[TD]38[/TD]
[TD]39[/TD]
[TD]40[/TD]
[TD]41[/TD]
[TD]42[/TD]
[TD]43[/TD]
[TD]44[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]comp.1data4[/TD]
[TD]46[/TD]
[TD]47[/TD]
[TD]48[/TD]
[TD]49[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]52[/TD]
[TD]53[/TD]
[TD]54[/TD]
[TD]55[/TD]
[TD]56[/TD]
[TD]57[/TD]
[TD]58[/TD]
[TD]59[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]comp.1data5[/TD]
[TD]61[/TD]
[TD]62[/TD]
[TD]63[/TD]
[TD]64[/TD]
[TD]65[/TD]
[TD]66[/TD]
[TD]67[/TD]
[TD]68[/TD]
[TD]69[/TD]
[TD]70[/TD]
[TD]71[/TD]
[TD]72[/TD]
[TD]73[/TD]
[TD]74[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]comp.2data1[/TD]
[TD]76[/TD]
[TD]77[/TD]
[TD]78[/TD]
[TD]79[/TD]
[TD]80[/TD]
[TD]81[/TD]
[TD]82[/TD]
[TD]83[/TD]
[TD]84[/TD]
[TD]85[/TD]
[TD]86[/TD]
[TD]87[/TD]
[TD]88[/TD]
[TD]89[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]comp.2data2[/TD]
[TD]91[/TD]
[TD]92[/TD]
[TD]93[/TD]
[TD]94[/TD]
[TD]95[/TD]
[TD]96[/TD]
[TD]97[/TD]
[TD]98[/TD]
[TD]99[/TD]
[TD]100[/TD]
[TD]101[/TD]
[TD]102[/TD]
[TD]103[/TD]
[TD]104[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD]comp.2data3[/TD]
[TD]106[/TD]
[TD]107[/TD]
[TD]108[/TD]
[TD]109[/TD]
[TD]110[/TD]
[TD]111[/TD]
[TD]112[/TD]
[TD]113[/TD]
[TD]114[/TD]
[TD]115[/TD]
[TD]116[/TD]
[TD]117[/TD]
[TD]118[/TD]
[TD]119[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]comp.2data4[/TD]
[TD]121[/TD]
[TD]122[/TD]
[TD]123[/TD]
[TD]124[/TD]
[TD]125[/TD]
[TD]126[/TD]
[TD]127[/TD]
[TD]128[/TD]
[TD]129[/TD]
[TD]130[/TD]
[TD]131[/TD]
[TD]132[/TD]
[TD]133[/TD]
[TD]134[/TD]
[TD]135[/TD]
[/TR]
[TR]
[TD]comp.2data5[/TD]
[TD]136[/TD]
[TD]137[/TD]
[TD]138[/TD]
[TD]139[/TD]
[TD]140[/TD]
[TD]141[/TD]
[TD]142[/TD]
[TD]143[/TD]
[TD]144[/TD]
[TD]145[/TD]
[TD]146[/TD]
[TD]147[/TD]
[TD]148[/TD]
[TD]149[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]comp.3data1[/TD]
[TD]151[/TD]
[TD]152[/TD]
[TD]153[/TD]
[TD]154[/TD]
[TD]155[/TD]
[TD]156[/TD]
[TD]157[/TD]
[TD]158[/TD]
[TD]159[/TD]
[TD]160[/TD]
[TD]161[/TD]
[TD]162[/TD]
[TD]163[/TD]
[TD]164[/TD]
[TD]165[/TD]
[/TR]
[TR]
[TD]comp.3data2[/TD]
[TD]166[/TD]
[TD]167[/TD]
[TD]168[/TD]
[TD]169[/TD]
[TD]170[/TD]
[TD]171[/TD]
[TD]172[/TD]
[TD]173[/TD]
[TD]174[/TD]
[TD]175[/TD]
[TD]176[/TD]
[TD]177[/TD]
[TD]178[/TD]
[TD]179[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]comp.3data3[/TD]
[TD]181[/TD]
[TD]182[/TD]
[TD]183[/TD]
[TD]184[/TD]
[TD]185[/TD]
[TD]186[/TD]
[TD]187[/TD]
[TD]188[/TD]
[TD]189[/TD]
[TD]190[/TD]
[TD]191[/TD]
[TD]192[/TD]
[TD]193[/TD]
[TD]194[/TD]
[TD]195[/TD]
[/TR]
[TR]
[TD]comp.3data4[/TD]
[TD]196[/TD]
[TD]197[/TD]
[TD]198[/TD]
[TD]199[/TD]
[TD]200[/TD]
[TD]201[/TD]
[TD]202[/TD]
[TD]203[/TD]
[TD]204[/TD]
[TD]205[/TD]
[TD]206[/TD]
[TD]207[/TD]
[TD]208[/TD]
[TD]209[/TD]
[TD]210[/TD]
[/TR]
[TR]
[TD]comp.3data5[/TD]
[TD]211[/TD]
[TD]212[/TD]
[TD]213[/TD]
[TD]214[/TD]
[TD]215[/TD]
[TD]216[/TD]
[TD]217[/TD]
[TD]218[/TD]
[TD]219[/TD]
[TD]220[/TD]
[TD]221[/TD]
[TD]222[/TD]
[TD]223[/TD]
[TD]224[/TD]
[TD]225[/TD]
[/TR]
</tbody>[/TABLE]

And I want to make it into like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]data1[/TD]
[TD]data2[/TD]
[TD]data3[/TD]
[TD]data4[/TD]
[TD]data5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]16[/TD]
[TD]31[/TD]
[TD]46[/TD]
[TD]61[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]17[/TD]
[TD]32[/TD]
[TD]47[/TD]
[TD]62[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]18[/TD]
[TD]33[/TD]
[TD]48[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]19[/TD]
[TD]34[/TD]
[TD]49[/TD]
[TD]64[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]20[/TD]
[TD]35[/TD]
[TD]50[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]21[/TD]
[TD]36[/TD]
[TD]51[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]22[/TD]
[TD]37[/TD]
[TD]52[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]23[/TD]
[TD]38[/TD]
[TD]53[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]24[/TD]
[TD]39[/TD]
[TD]54[/TD]
[TD]69[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]25[/TD]
[TD]40[/TD]
[TD]55[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]26[/TD]
[TD]41[/TD]
[TD]56[/TD]
[TD]71[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]27[/TD]
[TD]42[/TD]
[TD]57[/TD]
[TD]72[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]28[/TD]
[TD]43[/TD]
[TD]58[/TD]
[TD]73[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]29[/TD]
[TD]44[/TD]
[TD]59[/TD]
[TD]74[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]30[/TD]
[TD]45[/TD]
[TD]60[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]76[/TD]
[TD]91[/TD]
[TD]106[/TD]
[TD]121[/TD]
[TD]136[/TD]
[/TR]
[TR]
[TD]77[/TD]
[TD]92[/TD]
[TD]107[/TD]
[TD]122[/TD]
[TD]137[/TD]
[/TR]
[TR]
[TD]78[/TD]
[TD]93[/TD]
[TD]108[/TD]
[TD]123[/TD]
[TD]138[/TD]
[/TR]
[TR]
[TD]79[/TD]
[TD]94[/TD]
[TD]109[/TD]
[TD]124[/TD]
[TD]139[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]95[/TD]
[TD]110[/TD]
[TD]125[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]81[/TD]
[TD]96[/TD]
[TD]111[/TD]
[TD]126[/TD]
[TD]141[/TD]
[/TR]
[TR]
[TD]82[/TD]
[TD]97[/TD]
[TD]112[/TD]
[TD]127[/TD]
[TD]142[/TD]
[/TR]
[TR]
[TD]83[/TD]
[TD]98[/TD]
[TD]113[/TD]
[TD]128[/TD]
[TD]143[/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD]99[/TD]
[TD]114[/TD]
[TD]129[/TD]
[TD]144[/TD]
[/TR]
[TR]
[TD]85[/TD]
[TD]100[/TD]
[TD]115[/TD]
[TD]130[/TD]
[TD]145[/TD]
[/TR]
[TR]
[TD]86[/TD]
[TD]101[/TD]
[TD]116[/TD]
[TD]131[/TD]
[TD]146[/TD]
[/TR]
[TR]
[TD]87[/TD]
[TD]102[/TD]
[TD]117[/TD]
[TD]132[/TD]
[TD]147[/TD]
[/TR]
[TR]
[TD]88[/TD]
[TD]103[/TD]
[TD]118[/TD]
[TD]133[/TD]
[TD]148[/TD]
[/TR]
[TR]
[TD]89[/TD]
[TD]104[/TD]
[TD]119[/TD]
[TD]134[/TD]
[TD]149[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]105[/TD]
[TD]120[/TD]
[TD]135[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]151[/TD]
[TD]166[/TD]
[TD]181[/TD]
[TD]196[/TD]
[TD]211[/TD]
[/TR]
[TR]
[TD]152[/TD]
[TD]167[/TD]
[TD]182[/TD]
[TD]197[/TD]
[TD]212[/TD]
[/TR]
[TR]
[TD]153[/TD]
[TD]168[/TD]
[TD]183[/TD]
[TD]198[/TD]
[TD]213[/TD]
[/TR]
[TR]
[TD]154[/TD]
[TD]169[/TD]
[TD]184[/TD]
[TD]199[/TD]
[TD]214[/TD]
[/TR]
[TR]
[TD]155[/TD]
[TD]170[/TD]
[TD]185[/TD]
[TD]200[/TD]
[TD]215[/TD]
[/TR]
[TR]
[TD]156[/TD]
[TD]171[/TD]
[TD]186[/TD]
[TD]201[/TD]
[TD]216[/TD]
[/TR]
[TR]
[TD]157[/TD]
[TD]172[/TD]
[TD]187[/TD]
[TD]202[/TD]
[TD]217[/TD]
[/TR]
[TR]
[TD]158[/TD]
[TD]173[/TD]
[TD]188[/TD]
[TD]203[/TD]
[TD]218[/TD]
[/TR]
[TR]
[TD]159[/TD]
[TD]174[/TD]
[TD]189[/TD]
[TD]204[/TD]
[TD]219[/TD]
[/TR]
[TR]
[TD]160[/TD]
[TD]175[/TD]
[TD]190[/TD]
[TD]205[/TD]
[TD]220[/TD]
[/TR]
[TR]
[TD]161[/TD]
[TD]176[/TD]
[TD]191[/TD]
[TD]206[/TD]
[TD]221[/TD]
[/TR]
[TR]
[TD]162[/TD]
[TD]177[/TD]
[TD]192[/TD]
[TD]207[/TD]
[TD]222[/TD]
[/TR]
[TR]
[TD]163[/TD]
[TD]178[/TD]
[TD]193[/TD]
[TD]208[/TD]
[TD]223[/TD]
[/TR]
[TR]
[TD]164[/TD]
[TD]179[/TD]
[TD]194[/TD]
[TD]209[/TD]
[TD]224[/TD]
[/TR]
[TR]
[TD]165[/TD]
[TD]180[/TD]
[TD]195[/TD]
[TD]210[/TD]
[TD]225[/TD]
[/TR]
</tbody>[/TABLE]

Sorry for the big example. If there is a way to do this for one data (i.e. data1) it will be easy to do it for the other 4. I also know how to create a macro from the Developer Tab if it is easier to create a vba code. A formula of course is ok too. I tried for hours to find a way to do this but I couldn't...
Thank you very much for your help,
Nataly.

Rick Rothstein · Oct 19, 2013

Run this macro with the data sheet as the active sheet and change the output sheet name (hightlighted in red) to whatever sheet name (other than your data sheet) that you want the transposed data to go to....

Rich (BB code):

Sub TransposeData()
  Dim X As Long, R As Long
  R = 2
  With Sheets("Sheet2")
    .Range("A1:E1").Value = WorksheetFunction.Transpose(Range("A2:A6").Value)
    For X = 2 To Cells(Rows.Count, "A").End(xlUp).Row Step 5
      .Cells(R, 1).Resize(15, 5) = WorksheetFunction.Transpose(Cells(X, "B").Resize(5, 15).Value)
      R = R + 5
    Next
  End With
End Sub

Nataly92 · Oct 19, 2013

I tried the code but there were a couple of problems which I fixed them in a way. The traspose was made only for the 5 first years. I thought that because the rows for each company were 5 and the years were 15, that the last 10 years weren't copied or they were overcopied by the next company's data. So I used a macro that adds 10 rows every 5 rows, altered the code you provided to fit the new form of my archive and it worked just fine.
Thank you very much! If you hadn't helped me I would have been stuck there doing this manualy...

Rick Rothstein · Oct 19, 2013

Nataly92 said:
I tried the code but there were a couple of problems which I fixed them in a way. The traspose was made only for the 5 first years. I thought that because the rows for each company were 5 and the years were 15, that the last 10 years weren't copied or they were overcopied by the next company's data. So I used a macro that adds 10 rows every 5 rows, altered the code you provided to fit the new form of my archive and it worked just fine.
Thank you very much! If you hadn't helped me I would have been stuck there doing this manualy...

I am glad you got it working, but I am a little confused. I copy/pasted the data you included in your original message into a worksheet (with the first header cell going to cell A1) and then, when I run the macro I posted, it outputs the exact table you showed that you wanted from that data... are you saying it is not doing that for you?

hiker95 · Oct 19, 2013

Nataly92,

Welcome to the MrExcel forum.

With your raw data in worksheet Sheet1, the output you are looking for will be in a new worksheet Results.

Sample raw data in worksheet Sheet1:

Excel 2007

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

1998

1999

2000

2001

2002

2003

2004

2005

2006

2007

2008

2009

2010

2011

2012

2

comp.1data1

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

3

comp.1data2

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

4

comp.1data3

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

5

comp.1data4

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

6

comp.1data5

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

7

comp.2data1

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

8

comp.2data2

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

9

comp.2data3

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

10

comp.2data4

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

11

comp.2data5

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

12

comp.3data1

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

13

comp.3data2

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

14

comp.3data3

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

15

comp.3data4

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

16

comp.3data5

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

17

Sheet1

After the macro in a new worksheet Results:

Excel 2007

A

B

C

D

E

1

data1

data2

data3

data4

data5

2

1

16

31

46

61

3

2

17

32

47

62

4

3

18

33

48

63

5

4

19

34

49

64

6

5

20

35

50

65

7

6

21

36

51

66

8

7

22

37

52

67

9

8

23

38

53

68

10

9

24

39

54

69

11

10

25

40

55

70

12

11

26

41

56

71

13

12

27

42

57

72

14

13

28

43

58

73

15

14

29

44

59

74

16

15

30

45

60

75

17

76

91

106

121

136

18

77

92

107

122

137

19

78

93

108

123

138

20

79

94

109

124

139

21

80

95

110

125

140

22

81

96

111

126

141

23

82

97

112

127

142

24

83

98

113

128

143

25

84

99

114

129

144

26

85

100

115

130

145

27

86

101

116

131

146

28

87

102

117

132

147

29

88

103

118

133

148

30

89

104

119

134

149

31

90

105

120

135

150

32

151

166

181

196

211

33

152

167

182

197

212

34

153

168

183

198

213

35

154

169

184

199

214

36

155

170

185

200

215

37

156

171

186

201

216

38

157

172

187

202

217

39

158

173

188

203

218

40

159

174

189

204

219

41

160

175

190

205

220

42

161

176

191

206

221

43

162

177

192

207

222

44

163

178

193

208

223

45

164

179

194

209

224

46

165

180

195

210

225

47

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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Option Explicit
Sub ReorgData()
' hiker95, 10/19/2013
' http://www.mrexcel.com/forum/excel-questions/733822-traspose-each-row-every-5-rows-15-cells-into-one-column.html
Dim r As Long, lr As Long, lc As Long, nr As Long
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=Sheets("Sheet1")).Name = "Results"
With Sheets("Results")
  .UsedRange.ClearContents
  .Cells(1).Resize(, 5).Value = [{"data1","data2","data3","data4","data5"}]
End With
With Sheets("Sheet1")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  For r = 2 To lr Step 5
    nr = Sheets("Results").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    Sheets("Results").Range("A" & nr).Resize(lc - 1, 5).Value = Application.Transpose(.Range(.Cells(r, 2), .Cells(r + 4, lc)))
  Next r
End With
With Sheets("Results")
  .Columns.AutoFit
  .Activate
End With
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.

markmzz · Oct 19, 2013

Rick Rothstein said:
I am glad you got it working, but I am a little confused. I copy/pasted the data you included in your original message into a worksheet (with the first header cell going to cell A1) and then, when I run the macro I posted, it outputs the exact table you showed that you wanted from that data... are you saying it is not doing that for you?

Hi Rick,

Here is what I got with your code (in yellow):

[TABLE="width: 564"]
<tbody>[TR]
[TD="class: xl64, width: 72, bgcolor: yellow"]comp.1data1
[/TD]
[TD="class: xl64, width: 74, bgcolor: yellow"]comp.1data2
[/TD]
[TD="class: xl64, width: 74, bgcolor: yellow"]comp.1data3
[/TD]
[TD="class: xl64, width: 74, bgcolor: yellow"]comp.1data4
[/TD]
[TD="class: xl64, width: 74, bgcolor: yellow"]comp.1data5
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2002
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2003
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2004
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2005
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2006
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2007
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2008
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2009
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2010
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2011
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2012
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]1
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]16
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]31
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]46
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]61
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]11
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]13
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]14
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]2
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]17
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]32
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]47
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]62
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]21
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]22
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]23
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]24
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]25
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]26
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]27
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]28
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]29
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]30
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]3
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]18
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]33
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]48
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]63
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]35
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]36
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]37
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]38
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]39
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]40
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]41
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]42
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]43
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]44
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]45
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]4
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]19
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]34
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]49
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]64
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]50
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]51
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]52
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]53
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]54
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]55
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]56
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]57
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]58
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]59
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]60
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]5
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]20
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]35
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]50
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]65
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]65
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]66
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]67
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]68
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]69
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]70
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]71
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]72
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]73
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]74
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]75
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]21
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]22
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]23
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]24
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]25
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]80
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]81
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]82
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]84
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]86
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]88
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]89
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]90
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]36
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]37
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]38
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]39
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]40
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]95
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]96
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]97
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]98
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]99
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]100
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]101
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]102
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]103
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]105
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]51
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]52
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]53
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]54
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]55
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]110
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]111
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]112
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]113
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]114
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]115
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]116
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]117
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]118
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]119
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]120
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]66
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]67
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]68
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]69
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]70
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]125
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]126
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]127
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]128
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]129
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]130
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]131
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]132
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]133
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]134
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]135
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]80
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]95
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]110
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]125
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]140
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]140
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]141
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]142
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]143
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]144
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]145
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]146
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]147
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]148
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]149
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]150
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]96
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]97
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]98
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]99
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]100
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]155
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]156
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]157
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]158
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]159
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]160
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]161
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]162
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]163
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]164
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]165
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]111
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]112
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]113
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]114
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]115
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]170
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]171
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]172
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]173
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]174
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]175
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]176
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]177
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]178
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]179
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]180
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]126
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]127
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]128
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]129
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]130
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]185
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]186
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]187
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]188
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]189
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]190
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]191
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]192
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]193
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]194
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]195
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]141
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]142
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]143
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]144
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]145
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]200
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]201
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]202
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]203
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]204
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]205
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]206
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]207
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]208
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]209
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]210
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]155
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]170
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]185
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]200
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]215
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]215
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]216
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]217
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]218
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]219
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]220
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]221
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]222
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]223
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]224
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]225
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]156
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]171
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]186
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]201
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]216
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]157
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]172
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]187
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]202
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]217
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]158
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]173
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]188
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]203
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]218
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]159
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]174
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]189
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]204
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]219
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]160
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]175
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]190
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]205
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]220
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]161
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]176
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]191
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]206
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]221
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]162
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]177
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]192
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]207
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]222
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]163
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]178
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]193
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]208
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]223
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]164
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]179
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]194
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]209
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]224
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]165
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]180
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]195
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]210
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]225
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

I did a small modification (in red).

Code:

Sub TransposeData_mk()
  Dim X As Long, R As Long
  R = 2
  With Sheets("Sheet2")
    .Range([COLOR="#FF0000"]"R1:V1"[/COLOR]).Value = WorksheetFunction.Transpose(Range("A2:A6").Value)
    For X = 2 To .Cells(Rows.Count, "A").End(xlUp).Row Step 5
        .Cells(R, "R").Resize(15, 5) = WorksheetFunction.Transpose(Cells(X, "B").Resize(5, 15).Value)
        R = R + [COLOR="#FF0000"]15[/COLOR]
    Next
  End With
End Sub

I think it's ok now.

Markmzz

Rick Rothstein · Oct 19, 2013

markmzz said:
Hi Rick,

Here is what I got with your code (in yellow):

[TABLE="width: 564"]
<TBODY>[TR]
[TD="class: xl64, width: 72, bgcolor: yellow"]comp.1data1
[/TD]
[TD="class: xl64, width: 74, bgcolor: yellow"]comp.1data2
[/TD]
[TD="class: xl64, width: 74, bgcolor: yellow"]comp.1data3
[/TD]
[TD="class: xl64, width: 74, bgcolor: yellow"]comp.1data4
[/TD]
[TD="class: xl64, width: 74, bgcolor: yellow"]comp.1data5
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2002
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2003
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2004
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2005
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2006
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2007
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2008
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2009
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2010
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2011
[/TD]
[TD="class: xl63, width: 34, bgcolor: transparent, align: right"]2012
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]1
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]16
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]31
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]46
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]61
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]11
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]13
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]14
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]15
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]2
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]17
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]32
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]47
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]62
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]21
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]22
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]23
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]24
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]25
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]26
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]27
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]28
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]29
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]30
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]3
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]18
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]33
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]48
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]63
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]35
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]36
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]37
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]38
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]39
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]40
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]41
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]42
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]43
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]44
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]45
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]4
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]19
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]34
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]49
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]64
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]50
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]51
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]52
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]53
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]54
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]55
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]56
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]57
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]58
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]59
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]60
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]5
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]20
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]35
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]50
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]65
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]65
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]66
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]67
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]68
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]69
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]70
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]71
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]72
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]73
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]74
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]75
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]21
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]22
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]23
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]24
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]25
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]80
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]81
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]82
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]84
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]86
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]88
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]89
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]90
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]36
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]37
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]38
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]39
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]40
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]95
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]96
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]97
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]98
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]99
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]100
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]101
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]102
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]103
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]105
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]51
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]52
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]53
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]54
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]55
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]110
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]111
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]112
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]113
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]114
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]115
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]116
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]117
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]118
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]119
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]120
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]66
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]67
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]68
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]69
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]70
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]125
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]126
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]127
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]128
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]129
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]130
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]131
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]132
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]133
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]134
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]135
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]80
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]95
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]110
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]125
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]140
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]140
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]141
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]142
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]143
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]144
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]145
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]146
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]147
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]148
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]149
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]150
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]96
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]97
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]98
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]99
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]100
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]155
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]156
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]157
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]158
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]159
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]160
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]161
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]162
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]163
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]164
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]165
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]111
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]112
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]113
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]114
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]115
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]170
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]171
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]172
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]173
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]174
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]175
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]176
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]177
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]178
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]179
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]180
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]126
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]127
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]128
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]129
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]130
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]185
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]186
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]187
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]188
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]189
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]190
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]191
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]192
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]193
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]194
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]195
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]141
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]142
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]143
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]144
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]145
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]200
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]201
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]202
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]203
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]204
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]205
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]206
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]207
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]208
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]209
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]210
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]155
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]170
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]185
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]200
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]215
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]215
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]216
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]217
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]218
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]219
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]220
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]221
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]222
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]223
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]224
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]225
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]156
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]171
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]186
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]201
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]216
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]157
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]172
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]187
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]202
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]217
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]158
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]173
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]188
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]203
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]218
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]159
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]174
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]189
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]204
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]219
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]160
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]175
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]190
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]205
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]220
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]161
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]176
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]191
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]206
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]221
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]162
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]177
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]192
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]207
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]222
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]163
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]178
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]193
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]208
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]223
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]164
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]179
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]194
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]209
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]224
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow, align: right"]165
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]180
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]195
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]210
[/TD]
[TD="class: xl64, bgcolor: yellow, align: right"]225
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

I did a small modification (in red).

Code:

Sub TransposeData_mk() Dim X As Long, R As Long R = 2 With Sheets("Sheet2") .Range([COLOR=#000000]"R1:V1"[/COLOR]).Value = WorksheetFunction.Transpose(Range("A2:A6").Value) For X = 2 To .Cells(Rows.Count, "A").End(xlUp).Row Step 5 .Cells(R, [B][COLOR=#b22222]"R"[/COLOR][/B]).Resize(15, 5) = WorksheetFunction.Transpose(Cells(X, "B").Resize(5, 15).Value) R = R + [COLOR=#0000ff][B]15[/B][/COLOR] Next End With End Sub

You also changed the text I highlighted in red as well. But why did you change the 5 to 15 (highlighted in blue)? That would not match what the OP posted she wanted originally. As for the yellow highlighted part.... isn't that what the OP indicated she wanted in her original message?

markmzz · Oct 19, 2013

Rick Rothstein said:
You also changed the text I highlighted in red as well. But why did you change the 5 to 15 (highlighted in blue)? That would not match what the OP posted she wanted originally. As for the yellow highlighted part.... isn't that what the OP indicated she wanted in her original message?

1) Thanks. I forgot the "R" in Cells(R, "R").

2) Before, the data have 15 columns, then, after the use of the function TRANSPOSE, we have 15 rows. So you must to add 15 rows and not 5 rows for the next step. Look at the second table of the post #1.

Markmzz

Rick Rothstein · Oct 19, 2013

markmzz said:
2) Before, the data have 15 columns, then, after the use of the function TRANSPOSE, we have 15 rows. So you must to add 15 rows and not 5 rows for the next step. Look at the second table of the post #1.

You are right... a mental lapse on my part (I only got about 3 hours sleep last night and it looks like that may be showing in my posts). Thanks for noting that for me.

markmzz · Oct 20, 2013

Rick Rothstein said:
You are right... a mental lapse on my part (I only got about 3 hours sleep last night and it looks like that may be showing in my posts). Thanks for noting that for me.

You're welcome.

And sometimes, we all make mistakes, especially when we're tired.

Markmzz

Traspose each row every 5 rows (of 15 cells) into one column

Nataly92

New Member

Excel Facts

Rick Rothstein

MrExcel MVP

Nataly92

New Member

Rick Rothstein

MrExcel MVP

hiker95

Well-known Member

markmzz

MrExcel MVP

Rick Rothstein

MrExcel MVP

markmzz

MrExcel MVP

Rick Rothstein

MrExcel MVP

markmzz

MrExcel MVP

Similar threads

Forum statistics

Share this page

Traspose each row every 5 rows (of 15 cells) into one column

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

Well-known Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

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