Hi Jerry - been a long time, hope life is treating you well.

Ok, so here is my problem. Every month I run a report that gives me a count of errors for different error codes. What I do is rank the top 5 error codes according to their counts. The error codes are like H10012 or H20631, etc. Every once in a while, though, different error codes will have the same the number of errors. The Large function simply repeats the first error code if it has the same number of errors as another code.

Here's an example of the data

H10012 20


H10021 15


H20631 20


H30513 45


H40512 35


The codes are all in Column A and the counts can be in Columns B - M (Jan - Dec), depending upon the month that I am looking for (each month is in a different column). I'd rather avoid trying to use helper columns because I constantly change the number of columns and that would require a lot of work.

Right now I am doing this in two stages. First, find the largest 5 values:


=LARGE(M:M,1) In this case, looking in Column M (Dec) and find the largest value. Second:


=INDEX(A:A,MATCH(LARGE(M:M,1),M:M,0)) Return the corresponding error code associated with the largest value in Column M.

Again, I look for the top 5 largest values. And in the above list, I'd like to have the sequence be:

H10021 15


H10012 20


H20631 20


H40512 35


H30513 45


But when I try to solve for this problem using Large, H10012 gets repeated twice because 20 appears twice. I found this formula out on the internet but I cannot get it to work properly. Probably because my pivot table doesn't start on row 1; the first error code starts on row A6:


=INDEX(A:A,MATCH(LARGE(D$1:D$500+(ROW(D$1:D$500)*0 .0000000001),ROW(1:1)),D$1:D$500+(ROW(D$1:D$500)*0 .0000000001),0))



Any ideas?

Easiest would be to RANK the column M values first because you can resolve the ties within the RANK formula.

Assuming the first value is in M2 (M1 being the title), put thie formula in N2 and copy down. It will rank the value and break ties by giving preference to the first time a value appears in column M. So the tied for 3rd would go to the first time the value appears, the second time it happens that rank will increment to 4.

=RANK($M2,$M:$M)+ COUNTIF($M$1:$M2,$M2)-1

Now copy that down.

Last, the first formula you will use to grab the column A code that is the Rank1 in column N,

=INDEX(A:A, MATCH(ROW(A1),N:N, 0))

Copy that down and your ordered list will appear.

Thanks, Jerry, I went with a different approach but thanks for offering up yours... I'm sure it would work just fine. Incidentally, I'm having an issue with a VBA script I wrote so I'll start another thread to see if you can help with that.