Conditional Formatting to match any combination of values against the correct set of value

Hi there,

I am trying to develop a results table whereby people can guess the first 5 places in a race. I then want to enter in the correct positions once the race has been completed and for it to show me who was correct and if anyone managed to guess all 5 positions correctly. I have tried to use Conditional Formatting to return the same values, but I do not want any formatting to be applied if the cells containing the correct positions are blank (before the race has finished).

Below are the functions I want Excel to execute:

- Highlight any single position somebody guesses correctly
- Highlight the row when somebody gets all 5 positions correct
- Work out how many points each person receives depending on how many positions they guess right

Ideally I would like the highlighting of the cells to use Conditional Formatting and to not require any other data being entered (ie. a CONCATENATE formula), but this is just an ideal.

Any help would be greatly appreciated, I have attached a screenshot of the layout of the results table.

Thanks!

Sweepstake.jpg

Hi Richard,

My apologies, I was hardly expecting someone to create my workbook from scratch! I have attached the file in question.

Thanks!

Hi all,

I have managed to solve my issues and now have a good results table to highlight winners, automatically add up awarded points and mark the top 5 places.

The only thing that would be good to quickly see is whether or not any two people have exactly the same guesses for the race outcome.

Is there a way to do this so that blank cells are ignored even when there aren't any duplicate guesses..? With Conditional Formatting if possible?

I have attached a simple excel document with some mock data, including one set of duplicates and some blank cells (as not everyone listed will necessarily want to play).

Sweepstake.xlsx

Thanks!