# Highlight Duplicates Between Multiple Columns

1. ## Highlight Duplicates Between Multiple Columns

Hi,

I'm normally pretty good with Excel formulas but this one has got me stumped and wondered if any could help.

I'm running a football competition for my business where customers are required to guess World Cup football scores via Facebook. I'm storing all their entries in a spreadsheet.

In the spreadsheet column a is their name, b is the match (e.g. England v Italy), c is the home tame (England), d is the score (1-1), e is the away team (Italy)

Across from, that I have the actual fixtures taking place at the World Cup. So column I is the date, K is the home team (England), L is the actual score (2-0) and M is the away team (Italy)

What I need is a formula that when I enter the result on a game into the right set of columns (Columns I:M) it will highlight those Columns (Columns C:E) that have entered that correct score.

It needs to compare 3 columns and not just one because for example if it highlights the score (1:1 for example) it could be highlighting someone who picked Spain v Holland 1:1 where the correct result would be someone who picked England v Italy 1:1.

Does anyone know a formula I can use to do this?

Thanks,

John

2. ## Re: Highlight Duplicates Between Multiple Columns

I'd do it using two helper rows to make a unique entry for each result & then do an INDEX : MATCH. You can, of course, hide the helper rows.

See attached.

3. ## Re: Highlight Duplicates Between Multiple Columns

Hi,

That's great thank you.

However, does this only show one result? So if we were to have multiple people guess the same correct answer would it only show one rather than them all?

Thanks

4. ## Re: Highlight Duplicates Between Multiple Columns

You're completely right! That can be taken care of, but I'm sitting on a plane, about to take off. If no-one else provides the answer in the meantime, I'll do so after I get to my hotel in a couple of hours.

Thank you.

6. ## Re: Highlight Duplicates Between Multiple Columns

I hate being beaten. After a bumpy flight to London, I've agonised over this for an hour. BUT - IT WORKS. I hope.

If you move the columns around, the only thing that you MUST do, to make this ARRAY FORMULA work is to change the offset modifier (13 in muy equation - appearring twice) to be equal to the ccolumn number of the starting point of the array (so if you have the first answer cell in J, change the 13s to 10s.

Array formulae: set by CTRL + SHIFT + ENTER.

If this works, please mark the thread as solved and preferably hit the "Add Reputation" button. If it doesn't, well we can try again tomorrow afternoon in the airport!

7. ## Re: Highlight Duplicates Between Multiple Columns

Thank you that works perfectly.

I apologise for taking up your time and humbly thank you for all of your help. You've really done a fantastic job.

8. ## Re: Highlight Duplicates Between Multiple Columns

You're welcome. Also on the plus side, I can now do something on Excel that I couldn't do this time yesterday!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1