ID# Column Needs to Match the Line Number to the Score in the Score Column

[viclea]

I am attaching a worksheet that shows random triple scores. My problem is the ID# column CC. It not only matches and shows the ID# for the triple scores, but it is showing additional numbers. I do not know where these numbers come from and I do not want them. I only want it (Column CC) to show ID#s (line numbers) for the actual triple scores. I am sure it is something that I am just not seeing since, I have used the same formula for the consecutive scores and have changed the column letters. Another set of eyes will be appreciated.

Here is what I am trying to do. Column CB are scores taken from Column T and are listed in Column CB largest to smallest. Column CC are the line numbers for only those scores shown in Column CB. Column CD shows the position of the person in the list in Column AG. Then Column CE is the Full Name. Columns CC and CD should work the same way that Columns CH and CI

All of you who have helped me prepare this worksheet have provided tremendous help and learning. Thank you.

[JohnTopley]

in CC2

=IF(CB2=0,"",INDEX(ROW(T$2:$T$600),AGGREGATE(15,6,ROW($T$1:$T$600)/($T$2:$T$600=CB2),COUNTIF(CB$2:CB2,CB2))))

NOTE: column CB has zeros which are hidden by the format used [0;0;;@]

I recommend you change your thread title as it does not accurately describe your problem

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Administrative Note

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. Thanks.

[viclea]

I have tried to change the name and am sorry it did not reflect the subject.

[viclea]

I tried your formula and I get the "NAME" error. I am not sure why it doesn't work.

[JohnTopley]

My fault: you have Excel 2007 and Aggregate is not available until Excel 2010.

[JohnTopley]

Try

=IF(CB2=0,"",IFERROR(INDEX(ROW(T$2:$T$600),SMALL(IF($T$2:$T$600=CB2,ROW($T$2:$T$600)-ROW($T$2)+1,""),COUNTIF(CB$2:CB2,CB2)),0),""))

Enter with Ctrl+Shift+Enter

Corrected above: forgot about hidden zeros!

[viclea]

Thank you!!! It worked. I really appreciate your help.