Need a proper guru! My head hurts ;(

[xix_xix]

Hello everyone

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

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

'[image: http://www.imageupload.co.uk/images/2014/02/15/New1.jpg]
(http://www.imageupload.co.uk/D1h)


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 D1500 show the number of smaller prizes making up the priz
amount in corresponding C cell
Cells E1:E500 is a calculation cell to allow the name of winner to b
displayed in Cells L1:L500
Cells H1:K12 Is the table displaying the prize amount for that specifi
person broken up into smaller prizes and showing the ID of the prize an
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 displa
the names of the winners automatically in cells L1:L500, if a person ha
won 5 prizes it lists their name 5 times, however there is a sligh
problem with this as even if a player has won 5 prizes, 3 of the prize
could be the same, so instead of listing persons name 5 times, it shoul
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 wo
and display their name that number of times in Column L, but as state
above, and shown in the image of the worksheet, sometimes a person ca
win 5 prizes and some will be the same, so instead of showing th
persons name in the L column 5 times, it should only show the number o
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 priz
ID's in cells M2:M500 as well as how many of those prizes person has wo
in cells N2:N500

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

'[image: http://www.imageupload.co.uk/images/2014/02/15/new_2.jpg]
(http://www.imageupload.co.uk/D16)

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

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

Thank

 

[xix_xix]

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

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

Edit

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

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