Help generating two rankings for seperate subjects

[hassjohn]

Hi,

I need to generate 2 ranking list.
The session number is based on the date, while the trial number is based on the time within each session.
Also each session and trial list has to be based on the subject (name).

I attach an example with how i want the final result to look.

Very appreciative for help with creating a formula for this. The data consists of 3600+ trials as of now, from 40ish subjects, and more will be added.

Thanks,
/John

[JeteMc]

According to testing by matching expected values in the file attached to post #1, the following formulas work for Session and Trial respectively:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Let me know if you have any questions.

[hassjohn]

Hi and thanks for your help.

Im not sure if i understand the formula, as it includes the "D" column, which is suposse to be empty (I just included it to show how the final result should look).
So what formulas will generate the content in column D, using info from columns ABC (and maybe a 4th new column), and same goes for generating the content of column E.

I've tried using =SUMPRODUCT(--(A2=$A$2:$A$26),--(B2>$B$2:$B$26))+1
But then the list will skip ranks (1,1,1,1,5,5,7,7, etc)

When using =SUMPRODUCT(--(A6=$A$2:$A$26),(--(B6>B$2:B$26)))/COUNTIF(A$2:A$26,A6)+1
The list generated has decimals (1,1,1,1,1.5,1.5,1.75,1.75, etc)

[JeteMc]

Column D is ranking the sessions. It could be any column, for example column R, and the formula would simply change to include references to that column, for example:

Please Login or Register  to view this content.

It just seems easy to reference the sessions ranking column when compiling the trial ranking column, can't really understand the restriction to columns A, B and C in this instance. If the session ranking is in another column, as in the example above, then the formula would change to reference that column, as in:

Please Login or Register  to view this content.

Attached is your file with the formulas applied which may provide more clarity.

[hassjohn]

Ahh, Perfect!

I was confused and didnt realize that it was refering to itself.
Thanks a lot for the solution and your explanation.

/John

[Phuocam]

Or try this ...

In D2:

=IF(AND(A2=A1,B2=B1),D1,MAX(IF($A$1:A1=A2,$D$1:D1))+1)

Enter with Ctrl+Shift+Enter

In E2:

=IF(D2<>D1,1,E1+1)

[JeteMc]

You're Welcome, thank you for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

[hassjohn]

I noticed a dilemma with both formulas, and that is that it has to be sorted before applying the formulas. If I add new data later, they just get new session rank even though it has the same date as previous sessions. And if a date wasnt in order it follows the sequence.

The formula:
=1+COUNTIFS(A$2:A$26,A2,R$2:R$26,R2,C$2:C$26,"<"&C2)

takes though the time in to account, and returns the correct rank, even if it isnt sorted in advance.
=IF(D2<>D1,1,E1+1) Works though only if every thing is sorted, as it just adds one to the previous rank.

Any idea how to solve the issue with the Session ranking?

/John

[JeteMc]

Not sure that I understand what you are looking for. If I append the list by adding fp001 20130106 6:00:51 in A27:C27, and update the formulas to include that row, then even if it did show the correct session and time rankings, 3 and 1 (which it doesn't), wouldn't that be confusing as the values in the row above, fp005 20130223 5:28:58 also return 3 and 1. Seems as if it would be less confusing to sort the data by name and then date when new data is entered. Of course I may be missing the bigger picture here, and if so I'd suggest re-uploading your file with an example of the situation that is causing the dilemma, indicate where the formula(s) fail, and manually enter, again only where the formula(s) fail, values for session and time.
I do see one thing that need revising, to accommodate appending of the data, which is for both formulas to use whole column referencing as follows:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Let me know if you have any questions.

[hassjohn]

The problem was for row 34-37 & row 209-213, when subject VP023 had some extra sessions added that were not sorted.
In the raw data the date and time is in one column in this format (10/9/2013 5:50:32 PM), since we extract it from pdf files. Since its in single digit rathen then two digit format, the sorting doesnt really work. I fix it on a different sheet though, getting the YYYYMMDD format and seperating the time, and then use it for the ranking (which becomes the session/trial number). The data that i want to analyz remains though in the raw data, making me want to keep the original positions.

But i think I will just reorganize it a little, getting every thing on the samt sheet and use the sorting by name/date/time, as it would solves it.

Thanks for your help, really appreciated.

/John

[JeteMc]

You're Welcome, let us know if you need further assistance and best of luck on your project. Please take a moment to re-mark the thread as 'Solved'.