Need a proper guru! My head hurts ;(
 

Hello everyone

have been on some other excel forums and have been told that what I am looking for is possible, just no one knows exactly how to do it ;( - Am really hoping a proper guru is here to help?!

To try and explain (as best I can) -This shows the sheet I am working with




Cells A1:A500 show finishing positions for a competition
Cells B1:B500 show the name of the person in that finishing position
Cells C1:C500 show the prize amount that the person has won
Cells D1:D500 show the number of smaller prizes making up the prize amount in corresponding C cell
Cells E1:E500 is a calculation cell to allow the name of winner to be displayed in Cells L1:L500
Cells H1:K12 Is the table displaying the prize amount for that specific person broken up into smaller prizes and showing the ID of the prize and how many they have won of that ID

Cell G2 is a variable that depending what position is selected, different prizes are displayed in the table H1:K12

There are two issues I am having.

Firstly, after help on another forum, I have got a formula to display the names of the winners automatically in cells L1:L500, if a person has won 5 prizes it lists their name 5 times, however there is a slight problem with this as even if a player has won 5 prizes, 3 of the prizes could be the same, so instead of listing persons name 5 times, it should only list their name 3 times (3 different prizes)

The formula I got for these cells (L2:L500)is:
=INDEX($B$2:$B$901,MATCH(ROW()-ROW($D$2),$E$2:$E$901))

This is working together with the formula in Cells E2:E500
=SUM(D2, E2)

Which is able to correctly see how many prizes in total they have won and display their name that number of times in Column L, but as stated above, and shown in the image of the worksheet, sometimes a person can win 5 prizes and some will be the same, so instead of showing the persons name in the L column 5 times, it should only show the number of unique prizes (I am giving myself a headache)

In addition to this! (if that is not enough!!)


I am also having insane difficulty with:
M1:M500 and N1:N500

M1:M500 should display the prize ID that the person has won
N1:N500 should display how many of that prize the person has won

I need to find a formula that will automatically list the unique prize ID's in cells M2:M500 as well as how many of those prizes person has won in cells N2:N500

So at the end when everything is set up it looks like this:



Really hope this makes sense? Any questions or things I can explain better please just ask.

My head has nearly exploded several times trying to sort this. ;(

Thanks

If anyone needs to see the whole document to try work it out, I will be happy to send it - Must have a reputation on the forums + be remotely capable of finding a solution though.

One of toughest excel problems I have ever come across, I want to find a solution + find it some challenge, but I think this is beyond me

Edit*

I have now found a solution to the problem with displaying only the number of names in relation to unique prizes won - Created another column showing unique prizes and ran the other formula from that one instead of total prizes which works nicely.

Just the issue of displaying prize ID's and number of prizes won on the list ;( ;(