Got some problems on a sort and rank task

[Ashram]

I'm currently working on a researcher ranking/Impact factor spreadsheet.

Here's the deal :

I have a column with each cell being the authors list of various reviews.

eg. :

column A
revue a
revue b

column B
authorB, authorC, (...) , author(N-/+1)
authorA, authorB, authorC, (...) ,authorN

In col.B the authors are listed in an ordered manner.

My problem is : how to have a (or many) function(s) that would do the
following :

- 1 -
Look for an author (eg. authorA) in all cells in columnB

- 2 -
If authorA is in first or last place, we give him +1 point
If authorA is in second or (last place - 1) , we give him +0,5 points
If authorA is between second and (last place - 1), we give him +0,25 points.

- 3 -
Add up all points in order to rank the authors.

eg.
if authorA has been 2 times first, we give him +2 (2*1)
if authorA has been 3 times last, we give him +3 (3*1)
if authorA has been 4 times second, we give him +2 (4*0,5)
if authorA has been 1 time (last place - 1), we give him +0,5 (1*0,5)
if authorA has been 10 times between second and (last place - 1), we
give him +2,5 (10*0,25)
Total : 2+3+2+0,5+2,5 = 10

And doing this with all authors, we can have a ranking such as :
AuthorA = 10, AuthorB = 14, etc.

The problem is quite simple, but trying to have it solved by Excel is a
pain ...
Right now we have to do this by hand ... And it's LOOOOONNNNNNNNNNGGGGGGGG.

The lists are something like more than a thousand lists of authors,
each list having different numbers of authors (we have from 1 author to
more than 10).

Can someone help me with this one ?

For the moment I have managed to writ up a code, but it's bug-filled ....

 

[Bob Greenblatt]

I'm currently working on a researcher ranking/Impact factor spreadsheet.

Here's the deal :

I have a column with each cell being the authors list of various reviews.

eg. :

column A
revue a
revue b

column B
authorB, authorC, (...) , author(N-/+1)
authorA, authorB, authorC, (...) ,authorN

In col.B the authors are listed in an ordered manner.

My problem is : how to have a (or many) function(s) that would do the
following :

- 1 -
Look for an author (eg. authorA) in all cells in columnB

- 2 -
If authorA is in first or last place, we give him +1 point
If authorA is in second or (last place - 1) , we give him +0,5 points
If authorA is between second and (last place - 1), we give him +0,25 points.

- 3 -
Add up all points in order to rank the authors.

eg.
if authorA has been 2 times first, we give him +2 (2*1)
if authorA has been 3 times last, we give him +3 (3*1)
if authorA has been 4 times second, we give him +2 (4*0,5)
if authorA has been 1 time (last place - 1), we give him +0,5 (1*0,5)
if authorA has been 10 times between second and (last place - 1), we
give him +2,5 (10*0,25)
Total : 2+3+2+0,5+2,5 = 10

And doing this with all authors, we can have a ranking such as :
AuthorA = 10, AuthorB = 14, etc.

The problem is quite simple, but trying to have it solved by Excel is a
pain ...
Right now we have to do this by hand ... And it's LOOOOONNNNNNNNNNGGGGGGGG.

The lists are something like more than a thousand lists of authors,
each list having different numbers of authors (we have from 1 author to
more than 10).

Can someone help me with this one ?

For the moment I have managed to writ up a code, but it's bug-filled ....

This would be a whole lot easier if the authors appeared in a column by
themselves, i.e. Column B through Z for example. You then need another
column with a list of all the authors. Then it is pretty straightforward to
do the computations you want. With the sheet organized as you have now, some
pretty fancy parsing macros would be needed.