simple ranking / scoring system

hi all,

this is probably very easy to do but my brain is in a shut down / do not bother me mode.

I have 2 coloumns (SOURCE A and SOURCE B) with 2 different outputs that are rankings

IN SOURCE A, it ranks the 2 as the top choice, then 1, then 4 and lastly 5
IN SOURCE B, it ranks the 3 as the top choice, then comes 5, then 2 and lastly 1

I want to combine both SOURCE A and SOURCE B choices and create a new combined rank.
the basic way I think this can happen is assigning values to each ranking, for example

SOURCE A
"2" = 1
"1" = 2
"4" = 3
"5" = 4

SOURCE B
"3" = 1
"5" = 2
"2" = 3
"1" = 4

i want to combine both and achieve a new rank, score, I want the top 4.
example
2 has a score of 4
1 has a score 6
5 has a score 6
4 has a score 3 ( I want to omit this because it doesn't appear in both source columns, hence I want it ignored
3 has a score of 1 but once again omit/ignore it in calculations because it doesn't appear in both source coloumns

Please help, picture attached




excel example.PNG

Welcome to Excel Forum!

Give this a go, see if it helps.

Enter this formula in E2 and fill down

=IFERROR(AGGREGATE(15,6,(MATCH(ROW(E$1:E$5),$B$2:$B$5,0)/MATCH(ROW(E$1:E$5),$C$2:$C$5,0)>0)*ROW(E$1:E$5),ROWS(E$2:E2)),"")

Then this one in F2 and fill down

=IF(E2="","",MATCH(E2,$B$2:$B$5,0)+MATCH(E2,$C$2:$C$5,0))

Note that this will only work with positive integers, not decimals or negatives. The formula is set up for values of 1-5 as per your example, you can increase the maximum by changing the 3 row numbers in red. Note that all 3 should be changed equally to avoid errors.

that is PERFECT my friend

THANK YOU SO MUCH