selecting and copying data from columns where information occurs more than once

I recently participated in a golf pool where each participant in the pool (14 in total) were able to pick 10, PGA Tour players. We limited each PGA Tour player to be selected only 2 times amongst the 14 participants in the pool.

For the draft, I created a table of PGA players, earnings, wins, etc. called “PGA Big data” and I added an additional 3 columns. A column called “1st pick”, which had a drop down menu with each participants name; a column called “2nd pick”, which again had a drop down menu with each participants name; and a third column “picked”, which I used a “quick filter” to filter out the rows with two picks, so I could evaluate who was left for picking based on certain criteria. Everything worked great.

I filtered through the 1st and 2nd columns to compile a list of selected tour players for each participant. The filters worked great, but didn’t format the data the way I was hoping. I was also able to use pivot tables to filter each column which did the same thing and worked just as well, but again, it doesn’t format the data the way I need.

What I’d like is to search the “PGA Big data” table for each occurrence of a participants name (in the 1st pick and 2nd pick columns) and copy their selection of players into another table, “Ranking”, which is setup to show the selected players names and their earnings. The Ranking table totals each players earnings and ranks their total earnings against other participants to indicate 1st place, 2nd place, etc.

Here is a screenshot of my spreadsheet