I would like to preface this by saying that I am new to Excel and that I have tried looking up a solution to my problem online. Whilst I will continue trying to find a solution on my own I figured there is no harm posting here.
I will first describe the problem. My data consists of 20 columns. On the first row we have names, on the next thousand rows we have numbers. Each one of these thousands of rows represents an event, wherein the name with the highest number gets first place, the name with the second highest number gets second place and so on. My goal is to find how many times did name 1 get first place, repeat for name 2 and so on and then repeat that for every other place.
I thought if I could analyze one row to give me first the name with the highest number on that row and then the name with the second highest number and so on, I would have 20 columns wherein the first column would have the name with the highest number, the second column would have the name with the second highest number and so on. I would then expand that analysis down 999 rows and then I would have in that first column the names of all the first place finishers. I could then read that column and get the results, say name 1 had x number of first places and so on, then repeat for every other column. Unfortunately my limited Excel knowledge didn't allow me to do that.
I would appreciate any help either with making my solution work or with coming up with a better way to solve this. One less pressing issue is to find a way of dealing with ties but I have some ideas on that.
Bookmarks