I probably wouldn't use VBA for this. But, you can do the following
programmatically, if this list gets updated:

1. Make the following criteria range for each section you have
SEC
="=A"
2. Use Advanced Filter and the "Copy to New Location" option.
3. Repeat for each section
4. Sort each range
5. Use top n values that you need.
6. Concatenate ranges if desired.



On Jun 20, 6:10 am, Kiddio wrote:
like what OssieMac wrote
find all the positive value with the same sec than copy them to a new page
after which sort them in the order using sort, the first five it the one
with the lowest dept.

I also wont give u the code lol so read yourself from the link below

find method :http://msdn2.microsoft.com/de-de/lib...ce.interop.exc...

findnext method :http://msdn2.microsoft.com/de-de/lib...ce.interop.exc...

sort method :http://msdn2.microsoft.com/en-us/lib...ce.tools.excel...

Best Regards
Kiddio



"OssieMac" wrote:
I feel that this is probably a student assignment and as such I am reluctant
to give you code that you can simply copy. However, I will give you some
pointers to get started and perhaps others will give you some more or better
ones.

Copy the data to a new location (new worksheet)so that you do not loose the
original. Work with the copied data. It can be deleted later.

Sort on the SEC and DEBT column.

Find A in SEC column and then use find next within a loop to test each find
is zero and copy each valid line of data to another new area. Establish a
counter in the loop so that you know when you have the required 5. Repeat for
remaining SEC.

The procedure could be done with a loop within a loop. The first loop to
incremnent the SEC by commencing with A. Use ascii code to increment. (ascii
65 is A.) and incrementing it until J is reached. The second loop within
would be the previous paragraph.

Regards,

OssieMac

"student" wrote:

Friends,
I am writing to see whether you could please help with this VBA
coding.
The data below contains 10 SEC of totaling 500 obervations. We need to
choose 50:

For each SEC, choosing the five lowest DEBT among positive DEBT values
within this SEC, pulling together all these 50 observations,
then output containing PER, SIZE, SEC, DEBT, in ascending order of
DEBT.

Many many thanks.

CODE PER SIZE SEC DEBT
1 1000 298.84 A 101.94
2 1001 163.11 B 393.87
3 1002 267.79 C 115.78
4 1003 274.77 D 54.43
5 1004 269.03 E 126.18
6 1005 308.43 E 324.70
7 1006 365.95 E -55.93
8 1007 375.06 F 365.66
9 1008 548.37 B 110.42
10 1009 1215.41 E 137.37
11 1010 1264.68 D 116.36
12 1011 138.31 E 176.05
13 1012 139.72 G 139.89
14 1013 95.28 G 109.76
15 1014 76.46 F 190.52
16 1015 47.51 G 118.03
17 1016 56.07 B 353.27
18 1017 48.3 A 285.18
19 1018 40.06 D 51.41
20 1019 16.45 H 190.40
21 1020 16.13 E 164.52
22 1021 23.75 I 94.52
23 1022 22.23 C 768.47
24 1023 30.7 D 74.27
25 1024 29.63 F 123.65
26 1025 31.79 F 144.17
27 1026 31.72 D 135.14
28 1027 29.6 D 85.30
29 1028 33.54 A 145.79
30 1029 111.16 D 159.60
31 1030 110.61 B 138.36
32 1031 112.56 B 146.08
33 1032 109.67 J 67.19
34 1033 611.07 E 150.62
35 1034 646.16 I 139.63
36 1035 665.18 D 135.35
37 1036 677.48 J 76.04
38 1037 684.85 D -275.57
39 1038 696.98 C 147.17
40 1039 558.91 E 252.82
41 1040 1014.82 B 206.97
42 1041 1048.73 E 105.28
43 1042 253580.42 I 95.79
44 1043 663114 D 486.43
45 1044 812301.66 G 150.91
46 1045 124.98 D 68.32
47 1046 231.04 H 140.85
48 1047 398.7 E 235.23
49 1048 453.53 E 171.05
50 1049 409.84 C 99.98
51 1050 251.93 C 104.70
52 1051 220.43 D 391.17
53 1052 181.44 E 221.39
54 1053 3.66 A 121.57
55 1054 5050.37 I 201.77
56 1055 4157 J 75.16
57 1056 3856 G 105.73
58 1057 4331 D 72.23
59 1058 4307 D 132.85
60 1059 5360.21 B 202.23
61 1060 942.7 B 112.97
62 1061 1038.7 B 1180.42
63 1062 1075.2 B 165.40
64 1063 1407.2 D 101.60
65 1064 1510.7 D 63.11
66 1065 1451.7 B 170.06
67 1066 1241.4 C 130.04
68 1067 6392.77 G 127.38
69 1068 6028.89 C 114.48
70 1069 5388.44 C 184.11
71 1070 3628.7 B 528.44
72 1071 4479.38 B 187.18
73 1072 749.13 J 71.28
74 1073 649.73 C 92.79
75 1074 465.5 C 148.72
76 1075 5266.19 E 218.14
77 1076 8464.42 A 218.59
78 1077 7093.24 D 61.23
79 1078 4641.86 B -36.76
80 1079 1104.52 B 260.13
81 1080 969 E 357.24
82 1081 708.04 I 139.82
83 1082 916.75 C 97.26
84 1083 11868.8 A 124.05
85 1084 9872.12 A 222.63
86 1085 11577 E 817.61
87 1086 7757.51 I 122.35
88 1087 10053.2 D 81.96
89 1088 434.03 B 211.76
90 1089 356.48 C 117.52
91 1090 225.61 A 98.84
92 1091 1237.78 D 195.62
93 1092 1571.41 C 126.69
94 1093 1278.7 B 1383.10
95 1094 841.19 F 85.00
96 1095 975.27 C -3278.81
97 1096 468.75 H 101.23
98 1097 421.94 J 64.23
99 1098 294.14 J 67.13
100 1099 727.82 D 280.61
101 1100 721.91 D 59.52
102 1101 524.99 E 646.36
103 1102 11950.71 B 109.49
104 1103 12612.79 D 93.04
105 1104 11445.07 A 125.64
106 1105 7077.85 C -1815.91
107 1106 3317.76 E 160.10
108 1107 3179.91 D 81.59
109 1108 2972.39 D 87.55
110 1109 2727.23 H 123.52
111 1110 1915.44 E 217.02
112 1111 2428.82 C 185.97
113 1112 3979.65 I 144.97
114 1113 3539.78 F -268.99
115 1114 4224.23 C 217.64
116 1115 4085.31 I 160.39
117 1116 46837.59 I -65.96
118 1117 40331.45 E 289.48
119 1118 4.61 I 156.55
120 1119 2.12 C 222.11
121 1120 2.12 D 88.01
122 1121 12.24 D 146.28
123 1122 11.77 D 131.83
124 1123 11.74 E 192.62
125 1124 18.57 E 171.16
126 1125 18.88 I 160.08
127 1126 19.6 J 54.86
128 1127 22.29 J 151.12
129 1128 27.43 F 103.12
130 1129 28.1 I 115.21
131 1130 31.6 F 134.38
132 1131 36.66 E 138.51
133 1132 41.85 A 130.91
134 1133 42.44 E 136.69
135 1134 43.26 I 164.32
136 1135 50.55 D 65.51
137 1136 61.26 B 113.64
138 1137 56.86 A 103.73
139 1138 61.34 A 86.04
140 1139 61.34 I 153.71
141 1140 218.7 C 67.71
142 1141 218.51 A 132.53
143 1142 244.95 C 128.20
144 1143 350.08 I 101.89
145 1144 369.6 A 118.72
146 1145 503.85 E 157.29
147 1146 556.86 D 238.85
148 1147 577.12 J 86.04
149 1148 592.37 C 86.36
150 1149 665.81 C 129.82
151 1150 1076.9 C 109.22
152 1151 1076.9 C 333.45
153 1152 355 F 139.68
154 1153 342.74 A 148.12
155 1154 342.74 A 113.49
156 1155 1.95 G 84.99
157 1156 2.55 C 258.17
158 1157 1.38 F 145.74
159 1158 1.07 G 100.49
160 1159 0.73 F 88.91
161 1160 0.73 F -87.51
162 1161 2.5 D 105.44
163 1162 1.45 G 97.30
164 1163 2.48 C -103.58
165 1164 3.25 A 101.04
166 1165 3.25 E 242.59
167 1166 35.35 G 192.47
168 1167 56.22 F 105.07
169 1168 56.22 J 502.31
170 1169 403.21 E 1325.35
171 1170 423.98 E 160.11
172 1171 437.8 H 89.10
173 1172 472.91 E 197.44
174 1173 509.78 A 135.46
175 1174 547.42 J 73.46
176 1175 595.13 F 136.93
177 1176 595.13 J 116.90
178 1177 7.85 A 128.94
179 1178 10.44 D 1493.66
180 1179 11.05 C 132.75
181 1180 13.18 F 190.23
182 1181 17.79 B 188.62
183 1182 34.15 J 81.25
184 1183 100.19 C 148.15
185 1184 89.99 D 72.35
186 1185 89.778 D 159.45
187 1186 89.778 C 127.70
188 1187 5.468 D 138.23
189 1188 5.962 A 117.33
190 1189 7.881 E 252.12
191 1190 6.991 D 134.21
192 1191 9.335 E 209.16
193 1192 14.157 D 107.12
194 1193 25.009 F 114.48
195 1194 46.685 E 158.16
196 1195 86.294 A 236.05
197 1196 86.294 C -9.28
198 1197 86.294 B 240.43
199 1198 4.503 C 105.94
200 1199 2.155 F 145.45
201 1200 3.29 D 145.36
202 1201 4.253 G 65.31
203 1202 6.228 C 82.57
204 1203 6.13 C 144.33
205 1204 5.644 A 125.06
206 1205 5.833 A 124.90
207 1206 11.169 I 126.97
208 1207 9.79 C -48.76
209 1208 17.668 C 123.39
210 1209 13.482 D 86.18
211 1210 12.24 B 2732.34
212 1211 10.279 B -247.22
213 1212 14.124 D 69.00
214 1213 21.763 A 132.24
215 1214 21.341 C -83.21
216 1215 25.753 E 295.87
217 1216 13.088 A 129.50
218 1217 3.496 J 105.52
219 1218 1.902 C 107.86
220 1219 17.293 C 192.91
221 1220 24.63 C 208.40
222 1221 26.3 D 75.95
223 1222 25.8 C 131.31
224 1223 29.8 I 133.86
225 1224 28.9 G 57.77
226 1225 27.8 I 156.74
227 1226 27.2 J 73.81
228 1227 40.7 E 131.32
229 1228 46.335 C 172.16
230 1229 44.138 C 100.59
231 1230 39.7 A 147.05
232 1231 39.85 B 329.41
233 1232 43.56 D 274.64
234 1233 38.835 D 165.61
235 1234 35.391 B 145.57
236 1235 35.391 D 79.73
237 1236 35.391 C 360.90
238 1237 35.391 A 114.93
239 1238 35.391 B 156.13
240 1239 35.391 A 107.41
241 1240 35.391 F 92.21
242 1241 35.391 E 163.46
243 1242 35.391 C -796.65
244 1243 35.391 E 114.54
245 1244 35.391 G 132.83
246 1245 35.391 C 190.55
247 1246 35.391 G 41.17
248 1247 35.391 E 164.12
249 1248 35.391 A 157.34
250 1249 35.391 E 433.17
251 1250 35.391 D 275.99
252 1251 35.391 E -317.09
253 1252 35.391 B 120.84
254 1253 35.391 C 128.46
255 1254 35.391 C -174.06
256 1255 35.391 D 79.15
257 1256 35.391 E -92.46- Hide quoted text -

- Show quoted text -