multiple ranking (sorting), based on diff. criteria

E

Eddy Stan

from a big file of +5000 rows i have sized down to 400 rows (result), now i
want to rank them and show as main set1,set2,set3 & set4 (in one
column/called sets), in another column show as set1 01,set1 02...(called
setsq)
set1 should be dues +60 for top10 only
set2 should be dues +180 for dues <100k (excluding items of set1)
set3 should be dues +180 but dues <100k (excluding items of set1 & set2)
set4 should be dues <180 (excluding se1,set2 & set3)
** everywhere the dues in decending order
i have made up to set1, set2, set3 and set4
now i need help to show in column next to sets as setsq
as set1 01, set1 02... set1 10, then
as set2 01,set2 02.... until +180 dues >100k then
as set3 01,set3 02... until +180 dues <100k then
as set4 01,set4 01... until last - here first workout with +60 in decending
order then
general dues in decending order

sample as follows (paste in excel sheet) i require (formula for set UQ)
GP No Net Due Plus 60 Rank 60P Plus 180 Rank 180P SETS SETS UQ
5 17,486,282 10,523,258 1 8,537,108 1 SET1 SET1 01
8 10,870,882 8,554,027 2 8,526,243 2 SET1 SET1 02
159 7,818,267 7,561,135 3 3,404,847 5 SET1 SET1 03
127 4,318,103 4,318,103 4 4,318,103 3 SET1 SET1 04
87 7,871,655 3,918,829 5 99,717 42 SET1 SET1 05
58 3,827,557 3,827,557 6 3,827,557 4 SET1 SET1 06
16 9,774,170 3,720,683 7 1,970,861 7 SET1 SET1 07
19 20,175,740 3,601,623 8 621,477 15 SET1 SET1 08
86 11,752,130 3,455,255 9 84,978 46 SET1 SET1 09
1 14,797,019 3,343,600 10 406,041 20 SET1 SET1 10
30 6,253,425 2,286,361 12 2,182,796 6 SET2 SET2 01
45 1,347,080 1,347,080 15 1,379,080 8 SET2 SET2 02
105 961,374 961,374 20 1,221,465 9 SET2 SET2 03
74 5,159,543 1,326,871 16 1,010,275 11 SET2 SET2 04
29 1,074,911 1,074,911 19 1,074,911 10 SET2 SET2 05
93 377,868 577,868 30 788,993 12 SET2 SET2 06
2 1,705,155 706,797 22 698,838 13 SET2 SET2 07
78 623,214 623,214 26 623,214 14 SET2 SET2 08
119 8,704,805 700,476 23 522,339 16 SET2 SET2 09
4 2,986,730 1,186,198 17 520,316 17 SET2 SET2 10
22 477,155 477,155 33 477,155 18 SET2 SET2 11
59 440,198 440,198 34 440,198 19 SET2 SET2 12
9 2,725,486 501,825 31 349,206 21 SET2 SET2 13
41 312,812 312,812 40 312,812 22 SET2 SET2 14
81 294,779 294,779 41 294,779 23 SET2 SET2 15
146 274,687 274,687 46 274,687 24 SET2 SET2 16
57 263,349 263,349 48 263,349 25 SET2 SET2 17
25 1,437,809 268,032 47 240,032 26 SET2 SET2 18
116 2,244,178 2,038,934 13 234,917 27 SET2 SET2 19
112 2,407,758 489,798 32 231,018 28 SET2 SET2 20
234 369,636 369,636 36 221,802 29 SET2 SET2 21
76 2,389,990 328,302 39 159,422 30 SET2 SET2 22
92 147,604 143,104 60 143,104 31 SET2 SET2 23
193 365,023 365,023 37 142,923 32 SET2 SET2 24
26 6,077,524 200,218 55 140,102 33 SET2 SET2 25
77 128,977 128,977 62 128,977 34 SET2 SET2 26
124 125,056 125,056 63 125,056 35 SET2 SET2 27
34 507,561 124,669 64 118,602 36 SET2 SET2 28
152 112,000 112,000 69 112,000 37 SET2 SET2 29
158 109,044 109,044 71 109,044 38 SET2 SET2 30
111 102,000 102,000 74 102,000 39 SET2 SET2 31
104 287,801 287,801 43 101,967 40 SET2 SET2 32
47 52,198 52,198 95 99,802 41 SET3 SET3 01
68 93,610 93,610 77 93,610 43 SET3 SET3 02
42 93,064 93,064 78 93,064 44 SET3 SET3 03
125 90,000 90,000 79 90,000 45 SET3 SET3 04
55 76,688 76,688 84 76,688 47 SET3 SET3 05
67 74,580 74,580 86 74,580 48 SET3 SET3 06
100 61,253 61,449 92 66,924 49 SET3 SET3 07
195 66,824 66,824 88 66,824 50 SET3 SET3 08
51 48,756 48,756 97 48,756 51 SET3 SET3 09
79 47,187 47,187 98 47,187 52 SET3 SET3 10
24 585,044 240,558 51 46,610 53 SET3 SET3 11
10 2,320,177 162,552 57 46,158 54 SET3 SET3 12
129 46,000 46,000 99 46,000 55 SET3 SET3 13
190 45,000 45,000 100 45,000 56 SET3 SET3 14
12 349,392 120,127 67 42,556 57 SET3 SET3 15
126 39,877 39,877 101 39,877 58 SET3 SET3 16
178 39,600 39,600 102 39,600 59 SET3 SET3 17
33 1,636,567 88,126 80 39,267 60 SET3 SET3 18
70 38,779 38,779 104 38,779 61 SET3 SET3 19
130 33,800 33,800 106 33,800 62 SET3 SET3 20
114 3,301,548 180,457 56 32,639 63 SET3 SET3 21
3 204,452 28,288 113 28,288 64 SET3 SET3 22
113 2,636,717 878,430 21 24,928 65 SET3 SET3 23
101 322,132 24,279 117 24,279 66 SET3 SET3 24
80 16,800 16,800 122 16,800 67 SET3 SET3 25
106 103,685 16,409 123 16,250 68 SET3 SET3 26
184 16,250 16,250 124 16,250 68 SET3 SET3 27
99 16,963 16,963 121 15,371 70 SET3 SET3 28
72 14,797 14,797 125 14,797 71 SET3 SET3 29
64 14,024 14,024 128 14,024 72 SET3 SET3 30
189 12,209 12,209 131 12,209 73 SET3 SET3 31
91 121,693 81,700 82 11,400 74 SET3 SET3 32
157 10,000 10,000 133 10,000 75 SET3 SET3 33
177 9,550 9,550 135 9,550 76 SET3 SET3 34
117 1,053,031 608,970 28 8,771 77 SET3 SET3 35
199 441,900 78,400 83 7,900 78 SET3 SET3 36
188 7,300 7,300 138 7,300 79 SET3 SET3 37
128 7,000 7,000 139 7,000 80 SET3 SET3 38
73 6,742 6,742 140 6,742 81 SET3 SET3 39
210 6,000 6,000 141 6,000 82 SET3 SET3 40
53 5,467 5,467 143 5,467 83 SET3 SET3 41
118 5,550 5,550 142 4,566 84 SET3 SET3 42
7 3,656,864 9,542 136 4,242 85 SET3 SET3 43
49 51,742 3,783 145 3,783 86 SET3 SET3 44
243 3,400 3,400 148 3,190 87 SET3 SET3 45
66 2,765 2,765 150 2,765 88 SET3 SET3 46
95 927,777 222,709 53 2,619 89 SET3 SET3 47
69 2,418 2,418 151 2,418 90 SET3 SET3 48
246 2,350 2,350 152 2,350 91 SET3 SET3 49
90 2,002 2,002 154 2,002 92 SET3 SET3 50
85 508,752 66,740 89 2,000 93 SET3 SET3 51
28 2,299,668 2,250 153 1,913 94 SET3 SET3 52
248 702,254 610,015 27 1,900 95 SET3 SET3 53
207 1,600 1,600 155 1,600 96 SET3 SET3 54
161 205,510 81,925 81 1,503 97 SET3 SET3 55
63 1,500 1,500 156 1,500 98 SET3 SET3 56
32 340,437 2,889 149 1,497 99 SET3 SET3 57
231 137,957 137,957 61 1,442 100 SET3 SET3 58
148 19,300 1,300 157 1,300 101 SET3 SET3 59
97 210,914 9,969 134 827 102 SET3 SET3 60
203 112,403 27,775 115 449 103 SET3 SET3 61
200 2,494,642 1,184,911 18 393 104 SET3 SET3 62
202 226,241 122,382 66 218 105 SET3 SET3 63
27 878,952 208 161 208 106 SET3 SET3 64
245 1,279,702 589,133 29 15 107 SET3 SET3 65
252 1,464,215 2,669,019 11 108 SET4 SET4 001
259 3,842,798 1,555,257 14 108 SET4 SET4 002
122 1,884,980 699,300 24 108 SET4 SET4 003
284 1,842,250 652,660 25 108 SET4 SET4 004
255 626,530 374,822 35 108 SET4 SET4 005
194 3,037,686 343,960 38 108 SET4 SET4 006
249 432,000 290,000 42 108 SET4 SET4 007
289 298,011 285,054 44 108 SET4 SET4 008
342 1,035,600 279,900 45 108 SET4 SET4 009
14 839,051 250,441 49 108 SET4 SET4 010
198 716,811 248,939 50 108 SET4 SET4 011
268 273,754 232,504 52 108 SET4 SET4 012
263 212,600 212,600 54 108 SET4 SET4 013
296 687,980 154,100 58 108 SET4 SET4 014
258 143,319 143,319 59 108 SET4 SET4 015
293 124,626 124,626 65 108 SET4 SET4 016
281 360,000 120,000 68 108 SET4 SET4 017
287 124,962 111,943 70 108 SET4 SET4 018
272 106,500 106,500 72 108 SET4 SET4 019
242 105,600 105,600 73 108 SET4 SET4 020
297 101,800 101,800 75 108 SET4 SET4 021
334 148,400 97,000 76 108 SET4 SET4 022
288 285,040 75,704 85 108 SET4 SET4 023
294 365,910 70,000 87 108 SET4 SET4 024
304 2,047,368 64,600 90 108 SET4 SET4 025
285 74,483 62,445 91 108 SET4 SET4 026
278 1,382 61,000 93 108 SET4 SET4 027
204 301,999 58,189 94 108 SET4 SET4 028
260 161,930 49,800 96 108 SET4 SET4 029
282 39,000 39,000 103 108 SET4 SET4 030
266 126,800 37,600 105 108 SET4 SET4 031
262 83,736 32,250 107 108 SET4 SET4 032
21 945,495 32,200 108 108 SET4 SET4 033
209 31,300 31,300 109 108 SET4 SET4 034
279 134,000 31,000 110 108 SET4 SET4 035
18 29,564 29,564 111 108 SET4 SET4 036
283 1,139,265 28,739 112 108 SET4 SET4 037
176 81,200 28,200 114 108 SET4 SET4 038
257 561,592 26,070 116 108 SET4 SET4 039
267 23,842 23,842 118 108 SET4 SET4 040
274 185,100 23,000 119 108 SET4 SET4 041
89 408,771 19,571 120 108 SET4 SET4 042
250 11,680 14,550 126 108 SET4 SET4 043
165 20,499 14,525 127 108 SET4 SET4 044
56 12,850 12,850 129 108 SET4 SET4 045
261 12,763 12,763 130 108 SET4 SET4 046
286 11,300 11,300 132 108 SET4 SET4 047
88 164,332 8,300 137 108 SET4 SET4 048
292 4,892 4,892 144 108 SET4 SET4 049
240 964,845 3,645 146 108 SET4 SET4 050
276 3,510 3,510 147 108 SET4 SET4 051
205 463,200 1,000 158 108 SET4 SET4 052
237 700 700 159 108 SET4 SET4 053
300 989,024 162 108 SET4 SET4 054
332 742,506 162 108 SET4 SET4 055
6 638,926 162 108 SET4 SET4 056
254 612,355 162 108 SET4 SET4 057
325 600,888 162 108 SET4 SET4 058
305 550,000 162 108 SET4 SET4 059
290 526,332 162 108 SET4 SET4 060
316 398,525 162 108 SET4 SET4 061
132 392,441 162 108 SET4 SET4 062
308 301,950 162 108 SET4 SET4 063
310 299,209 162 108 SET4 SET4 064
329 264,600 162 108 SET4 SET4 065
341 228,200 162 108 SET4 SET4 066
323 227,037 162 108 SET4 SET4 067
269 226,899 162 108 SET4 SET4 068
339 201,403 162 108 SET4 SET4 069
315 183,536 162 108 SET4 SET4 070
327 112,479 162 108 SET4 SET4 071
326 111,832 162 108 SET4 SET4 072
307 90,500 162 108 SET4 SET4 073
340 85,250 162 108 SET4 SET4 074
311 83,741 162 108 SET4 SET4 075
313 79,578 162 108 SET4 SET4 076
251 40,699 162 108 SET4 SET4 077
338 71,000 162 108 SET4 SET4 078
241 68,000 162 108 SET4 SET4 079
330 49,652 162 108 SET4 SET4 080
302 49,550 162 108 SET4 SET4 081
98 49,180 162 108 SET4 SET4 082
336 48,500 162 108 SET4 SET4 083
333 44,569 162 108 SET4 SET4 084
314 43,927 162 108 SET4 SET4 085
343 43,165 162 108 SET4 SET4 086
306 43,000 162 108 SET4 SET4 087
331 42,775 162 108 SET4 SET4 088
312 41,866 162 108 SET4 SET4 089
174 41,700 162 108 SET4 SET4 090
324 38,600 162 108 SET4 SET4 091
280 33,300 162 108 SET4 SET4 092
140 32,000 162 108 SET4 SET4 093
320 23,020 162 108 SET4 SET4 094
337 23,000 162 108 SET4 SET4 095
344 22,100 162 108 SET4 SET4 096
301 21,000 162 108 SET4 SET4 097
335 20,000 162 108 SET4 SET4 098
328 19,000 162 108 SET4 SET4 099
319 16,664 162 108 SET4 SET4 100
295 13,290 162 108 SET4 SET4 101
37 7,500 162 108 SET4 SET4 102
270 7,000 162 108 SET4 SET4 103
303 1,113 162 108 SET4 SET4 104
318 561 162 108 SET4 SET4 105
60 162 108 SET4 SET4 106
144 162 108 SET4 SET4 107
196 162 108 SET4 SET4 108
275 162 108 SET4 SET4 109
299 162 108 SET4 SET4 110
291 (178) (178) 220 108 SET4 SET4 111
309 (2,500) 162 108 SET4 SET4 112
96 (280) (280) 221 (280) 220 SET3 SET3 66
201 (977) (977) 222 (977) 221 SET3 SET3 67
136 (1,685) (1,685) 223 (1,685) 222 SET3 SET3 68
138 (3,500) (3,500) 224 (3,500) 223 SET3 SET3 69
15 1,302,662 550 160 (6,000) 224 SET3 SET3 70
44 (11,284) (11,284) 225 (11,284) 225 SET3 SET3 71
120 436,510 (11,490) 226 (11,490) 226 SET3 SET3 72
40 (21,562) (21,562) 227 (21,562) 227 SET3 SET3 73
82 1,202,744 (314,627) 228 (389,771) 228 SET3 SET3 74
LLINE LLINE LLINE LLINE LLINE LLINE LLINE LLINE
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help formula (ranking) 1

Top