Trying to rate items from top point item to low point item ... plus ...

[RVT]

Hello,

I posted this situation on a previous post but not only did my computer crash and I lost the info but can not find it on here going back 70 pages and looking for it.

What I am trying to do here is to have excel assign a value to an entry and give a tally from top to bottom of that item and in addition to state how many columns that entry is listed in.

Example:

02 - 08 - 02 - 08 - 04 - 05 - 04
04 - 07 - 07 - 07 - 02 - 04 - 01
08 - 02 - 04 - 02 - 01 - 02 - 06
01 - 04 - 01 - 00 - 06 - 01 - 05
07

02 would get 5 points from column A, 2 points from column B, 4 points from column C, 1 point from column D as 00 represents a blank cell, 3 points from column E, 2 points from column F and 0 points from column G for a total of 17 points and a rating of 06 as it is listed in 6 of the 7 columns.

If there are 5 entries in a column points are 5, 4, 3, 2 and 1.
If there are 4 entries in a column points are 4, 3, 2 and 1 etc.

Attached is a work sheet that gives this example.

Previously three people helped me with this but I could not get it to work. They certainly were on the right track but either something was missing or more likely I WAS ...

Thank you for your help with this one,
Richard

[RVT]

P. S.

Please not that I am trying to do this for version 1 including columns A, B and C without having to know how many times the item was listed in the three columns and then version 2 using all 7 columns and having the number of columns that an item is listed in going along with that version.

[Olly]

Clumsy solution attached, calculating score and rating for all 7 columns. It assumes that each number may only appear once at most in each column.

[RVT]

What I do not understand like the solutions that were posted the last time is how excel comes up with column J numbers. Is there a way to have the excel program post the numbers in column J?