Reply to thread

Message: [QUOTE="Eddy Stan, post: 3845562"] 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 [/QUOTE]

Verification