Here is my code:
Excel 2002
I need a way to break the ties, so that it won't repeat the same bin, but instead it will display the other bin number as well
I think I need a code similar to:
=INDEX(Code!BK:BK,100000*MOD(LARGE(((Code!$DB:$DB)+(ROW(Code!$DB:$DB)/100000)),ROW(A2)),1),1)
however using this code simply returned a #NUM error.
Column BK lists all of the various Bin numbers,
and column DB lists the total tickets for each bin, both are currently on a pivot table, so not sure if that could be a source of error.<!-- / message -->
Excel Workbook | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
F | G | H | I | J | K | L | M | N | O | P | |||
81 | Top 25 Daily: | Total tickets | |||||||||||
82 | 1 | 765182654 | 84 | ||||||||||
83 | 2 | 765163654 | 77 | ||||||||||
84 | 3 | 764282604 | 56 | ||||||||||
85 | 4 | 765182753 | 54 | ||||||||||
86 | 5 | 765183454 | 48 | ||||||||||
87 | 6 | 765183454 | 48 | ||||||||||
88 | 7 | 771041704 | 45 | ||||||||||
89 | 8 | 793050956 | 40 | ||||||||||
90 | 9 | 773010350 | 39 | ||||||||||
91 | 10 | 765172952 | 37 | ||||||||||
92 | 11 | 763020005 | 36 | ||||||||||
93 | 12 | 763020005 | 36 | ||||||||||
94 | 13 | 763020005 | 36 | ||||||||||
95 | 14 | 764282901 | 33 | ||||||||||
96 | 15 | 764282901 | 33 | ||||||||||
97 | 16 | 792031502 | 30 | ||||||||||
98 | 17 | 792021859 | 29 | ||||||||||
99 | 18 | 764297057 | 28 | ||||||||||
100 | 19 | 771172152 | 27 | ||||||||||
101 | 20 | 764283701 | 25 | ||||||||||
102 | 21 | 764283701 | 25 | ||||||||||
103 | 22 | 793012659 | 23 | ||||||||||
104 | 23 | 793012659 | 23 | ||||||||||
105 | 24 | 765160759 | 22 | ||||||||||
106 | 25 | 765170451 | 21 | ||||||||||
Purdue Actual Tool |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I82 | =INDEX(Code!BK:DB,MATCH($M82,Code!$DB:$DB,0),1) | |
I83 | =INDEX(Code!BK:DB,MATCH($M83,Code!$DB:$DB,0),1) | |
I84 | =INDEX(Code!BK:DB,MATCH($M84,Code!$DB:$DB,0),1) | |
I85 | =INDEX(Code!BK:DB,MATCH($M85,Code!$DB:$DB,0),1) | |
I86 | =INDEX(Code!BK:DB,MATCH($M86,Code!$DB:$DB,0),1) | |
I87 | =INDEX(Code!BK:DB,MATCH($M87,Code!$DB:$DB,0),1) | |
I88 | =INDEX(Code!BK:DB,MATCH($M88,Code!$DB:$DB,0),1) | |
I89 | =INDEX(Code!BK:DB,MATCH($M89,Code!$DB:$DB,0),1) | |
I90 | =INDEX(Code!BK:DB,MATCH($M90,Code!$DB:$DB,0),1) | |
I91 | =INDEX(Code!BK:DB,MATCH($M91,Code!$DB:$DB,0),1) | |
I92 | =INDEX(Code!BK:DB,MATCH($M92,Code!$DB:$DB,0),1) | |
I93 | =INDEX(Code!BK:DB,MATCH($M93,Code!$DB:$DB,0),1) | |
I94 | =INDEX(Code!BK:DB,MATCH($M94,Code!$DB:$DB,0),1) | |
I95 | =INDEX(Code!BK:DB,MATCH($M95,Code!$DB:$DB,0),1) | |
I96 | =INDEX(Code!BK:DB,MATCH($M96,Code!$DB:$DB,0),1) | |
I97 | =INDEX(Code!BK:DB,MATCH($M97,Code!$DB:$DB,0),1) | |
I98 | =INDEX(Code!BK:DB,MATCH($M98,Code!$DB:$DB,0),1) | |
I99 | =INDEX(Code!BK:DB,MATCH($M99,Code!$DB:$DB,0),1) | |
I100 | =INDEX(Code!BK:DB,MATCH($M100,Code!$DB:$DB,0),1) | |
I101 | =INDEX(Code!BK:DB,MATCH($M101,Code!$DB:$DB,0),1) | |
I102 | =INDEX(Code!BK:DB,MATCH($M102,Code!$DB:$DB,0),1) | |
I103 | =INDEX(Code!BK:DB,MATCH($M103,Code!$DB:$DB,0),1) | |
I104 | =INDEX(Code!BK:DB,MATCH($M104,Code!$DB:$DB,0),1) | |
I105 | =INDEX(Code!BK:DB,MATCH($M105,Code!$DB:$DB,0),1) | |
I106 | =INDEX(Code!BK:DB,MATCH($M106,Code!$DB:$DB,0),1) | |
M82 | =LARGE(Code!$DB:$DB,ROW(A2)) | |
M83 | =LARGE(Code!$DB:$DB,ROW(A3)) | |
M84 | =LARGE(Code!$DB:$DB,ROW(A4)) | |
M85 | =LARGE(Code!$DB:$DB,ROW(A5)) | |
M86 | =LARGE(Code!$DB:$DB,ROW(A6)) | |
M87 | =LARGE(Code!$DB:$DB,ROW(A7)) | |
M88 | =LARGE(Code!$DB:$DB,ROW(A8)) | |
M89 | =LARGE(Code!$DB:$DB,ROW(A9)) | |
M90 | =LARGE(Code!$DB:$DB,ROW(A10)) | |
M91 | =LARGE(Code!$DB:$DB,ROW(A11)) | |
M92 | =LARGE(Code!$DB:$DB,ROW(A12)) | |
M93 | =LARGE(Code!$DB:$DB,ROW(A13)) | |
M94 | =LARGE(Code!$DB:$DB,ROW(A14)) | |
M95 | =LARGE(Code!$DB:$DB,ROW(A15)) | |
M96 | =LARGE(Code!$DB:$DB,ROW(A16)) | |
M97 | =LARGE(Code!$DB:$DB,ROW(A17)) | |
M98 | =LARGE(Code!$DB:$DB,ROW(A18)) | |
M99 | =LARGE(Code!$DB:$DB,ROW(A19)) | |
M100 | =LARGE(Code!$DB:$DB,ROW(A20)) | |
M101 | =LARGE(Code!$DB:$DB,ROW(A21)) | |
M102 | =LARGE(Code!$DB:$DB,ROW(A22)) | |
M103 | =LARGE(Code!$DB:$DB,ROW(A23)) | |
M104 | =LARGE(Code!$DB:$DB,ROW(A24)) | |
M105 | =LARGE(Code!$DB:$DB,ROW(A25)) | |
M106 | =LARGE(Code!$DB:$DB,ROW(A26)) |
I need a way to break the ties, so that it won't repeat the same bin, but instead it will display the other bin number as well
I think I need a code similar to:
=INDEX(Code!BK:BK,100000*MOD(LARGE(((Code!$DB:$DB)+(ROW(Code!$DB:$DB)/100000)),ROW(A2)),1),1)
however using this code simply returned a #NUM error.
Column BK lists all of the various Bin numbers,
and column DB lists the total tickets for each bin, both are currently on a pivot table, so not sure if that could be a source of error.<!-- / message -->