Rank Text Values in Ascending Order or Custom Order
Rank Text Values in Custom Order – Multiple Occurrences to Increment Rank
Column E: Column A contains Text Values in Random Order. Rank column A values in Custom Order ie. per cells D2:D5 order (cell D2 – rank 1, cell D3 – rank 2, & so on), and increment rank for repeat occurrences of same value.
———————————————————————————————–
Column E Ranks column A values in Custom Order ie. per cells D2:D5 order (cell D2 – rank 1, cell D3 – rank 2, & so on), and increments rank for repeat occurrences of same value.
Cell E2 formula:
=SUMPRODUCT(–(INDEX( MATCH( A$2:A$12, D$2:D$5,0)+ ROW( A$2:A$12)/10^7, ROWS( A$2:A2))>= MATCH( A$2:A$12, D$2:D$5,0)+ ROW(A$2:A$12)/ 10^7))
Column E formula – enter in cell E2 and copy down:
1. Rank column A values in Custom Order ie. per cells D2:D5 order (cell D2 – rank 1, cell D3 – rank 2, & so on), and increment rank for repeat occurrences of same value.
2. Column A consists of Names, these are to ranked in the order of cells D2:D5 which may not be in alphabetical order. If a name repeats itself in subsequent row(s), rank of each repetition will increment by 1, & the next name’s rank will continue after the last repetition. For ex. the 1st Occurrence of cell D2 (“Steve”) in column A (ie. cell A3) will be ranked 1 (cell E3), the 2nd occurrence of “Steve” (cell A8) will be ranked 2 (cell E8), and so on.
3. Cells D2:D5 should contain ALL names appearing in the range A2:A20. There can be additional names in D2:D5 which may not appear in A2:A20 but each A2:A20 name should be included.
4. Enter formula in cell E2 and copy down – this specifies the precise range A2:A12 and does not allow blank spaces within the range A2:A12.
Explanation – Cell E2 formula (Non-Array formula):
1) MATCH(A$2:A$12, D$2:D$5, 0): returns the rank / occurrence order of column A values vis-à-vis cells D2:D5 ie. {3;1;4; 2;4;4; 1;1;3; 1;2} – where the 1st 3 indicates that cell A2 value of “John” is ranked 3 (ie. the 3rd cell – D4 – in D2:D5 range), the next 1 indicates that cell A3 value of “Steve” is ranked 1 (ie. the 1st cell – D2 – in D2:D5 range), and so on.
2) ROW( A$2:A$12)/ 10^7: returns an array of very small values – {0.0000002; 0.0000003; 0.0000004; 0.0000005; 0.0000006; 0.0000007; 0.0000008; 0.0000009; 0.000001; 0.0000011; 0.0000012} – this divides each row number for cells A2:A12 by 10^7 and when added to the rank in 1) above, these provide a miniscule variation between similar values in column A to ensure an incremental rank for subsequent occurrences.
3) MATCH(A$2:A$12, D$2:D$5,0)+ ROW( A$2:A$12)/ 10^7: returns an array of values – {3.0000002; 1.0000003; 4.0000004; 2.0000005; 4.0000006; 4.0000007; 1.0000008; 1.0000009; 3.000001; 1.0000011; 2.0000012} – these are the adjusted ranks for each value in cells A2:A12 arrived at by adding 1) and 2) above. Note that the adjusted rank (3.0000002) of 1st occurrence of cell A2 value (“John”) will be less than the adjusted rank (3.000001) of next occurence of the same value (“John” occurs next in cell A10) because of the incremental row number of the next occurrence ie. ROW(A10) is greater than ROW(A2).
4) INDEX( MATCH( A$2:A$12, D$2:D$5, 0)+ ROW(A$2:A$12)/ 10^7, ROWS(A$2:A2)): INDEX function is used where ROWS(A$2:A2) references the 1st value’s (cell A2) adjusted rank arrived in 2) above ie. returns 3.0000002.
5) (INDEX(MATCH(A$2:A$12, D$2:D$5,0)+ ROW(A$2:A$12)/ 10^7, ROWS(A$2:A2))>= MATCH(A$2:A$12, D$2:D$5,0)+ ROW(A$2:A$12)/ 10^7): compares the cell A2’s adjusted rank with adjusted ranks of all column A names ie. compares 4) with 3) above, and returns an array of TRUE and FALSE values – {TRUE; TRUE; FALSE;TRUE; FALSE;FALSE; TRUE;TRUE; FALSE;TRUE; TRUE} – where TRUE indicates that cell A2 adjusted rank is greater than or equal to other column A names’ adjusted ranks. Using double negation converts TRUE to 1 and using SUMPRODUCT function determines the final rank of cell A2 name by adding all the 1s.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Column A contains Text Values in Random Order, Column I contains Month Names in Random Order.
Column B: Rank column A values in Ascending Order (alphabetically) and increment rank for repeat occurrences of same value;
Columns E:G: Rank column A values in Custom Order ie. per cells D2:D5 order (cell D2 – rank 1, cell D3 – rank 2, & so on), and increment rank for repeat occurrences of same value;
Columns J:K: Rank column I Month names in Ascending Order (Jan-Dec order) and increment rank for repeat occurrences of same month name;
Column F: Cell F2 formula uses the INDEX, MATCH, ROW, IFERROR and SUM functions is entered as an Array formula (Ctrl+Shift+Enter). Ranks column A values in Custom Order ie. per cells D2:D5 order (cell D2 – rank 1, cell D3 – rank 2, & so on), and increment rank for repeat occurrences of same value – similar to cell E2 formula with same conditions, except that cell F2 formula allows blank spaces after the continuous non-blank cells of A2:A12 (but there should not be inbetween blanks in A2:A12).
Column G: Cell G2 formula uses the INDEX, MATCH, ROW, IFERROR, COUNTIF and SUM functions is entered as an Array formula (Ctrl+Shift+Enter). Ranks column A values in Custom Order ie. per cells D2:D5 order (cell D2 – rank 1, cell D3 – rank 2, & so on), and increment rank for repeat occurrences of same value – similar to cell E2 formula with same conditions, except that cell G2 formula allows blank spaces after the continuous non-blank cells of A2:A12 (there should not be inbetween blanks in A2:A12).
Column J: Cell J2 formula uses the MONTH, DATEVALUE, COUNTIF functions with SUMPRODUCT and is entered as a Non-Array formula. Ranks column I Months in Ascending Order (Monthwise) and increments rank for repeat occurrences of same month name. It Ranks Column I Month Names in the order of calendar months (Jan – rank 1, Feb – rank 2, …). If a month name repeats itself in subsequent row(s), rank of each repetition will increment by 1, and the next month name’s rank will continue after the last repetition. It specifies the precise range I2:I12 and does not allow blank spaces within the range I2:I12.
Column K: uses the MONTH, DATEVALUE, COUNTIF functions with SUM and is entered as an Array formula (Ctrl+Shift+Enter). Ranks column I Months in Ascending Order (Monthwise) and increments rank for repeat occurrences of same month name. It is similar to cell J2 formula except that it covers the range I2:I20 & allows blank spaces after the continuous non-blank cells of I2:I12 (there should not be inbetween blanks in I2:I12).