+ Reply to Thread
Results 1 to 8 of 8

Highlight Duplicates Between Multiple Columns

  1. #1
    Registered User
    Join Date
    06-09-2014
    Posts
    6

    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. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    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.
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    06-09-2014
    Posts
    6

    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. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    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.

  5. #5
    Registered User
    Join Date
    06-09-2014
    Posts
    6

    Re: Highlight Duplicates Between Multiple Columns

    Thank you.

    Sorry for taking up your time and enjoy your flight!

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    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. #7
    Registered User
    Join Date
    06-09-2014
    Posts
    6

    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. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to compare two columns and highlight and rows containing duplicates
    By ks100 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-12-2014, 04:47 PM
  2. Replies: 3
    Last Post: 05-31-2013, 08:03 AM
  3. Highlight Duplicates from Two Columns with Different Colours
    By ddgacic in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-24-2012, 06:22 AM
  4. Replies: 5
    Last Post: 07-05-2011, 06:25 PM
  5. find duplicates in two columns and highlight whole row
    By stryker9603 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-22-2010, 01:46 PM

Bookmarks

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