Results 1 to 5 of 5

Thread: Help!! find two number recurring numbers in row??

  1. #1

    Talking Help!! find two number recurring numbers in row??



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi everyone, new to posting on this forum so thank you for your patience.
    I have a spreadsheet which has data in 7 columns and around 100 rows. I want to be able to find what two numbers appear most frequent together on each of the rows. They donít have to appear side by side they can appear anywhere on the row. It is kind of like the mode but instead of finding which number is most frequent it is to find what two numbers appear most frequent on each row? To give a better idea of what i mean I have attached some sample data. The red highlighted numbers of 26 & 29 appear 5 times on separate rows so I would want Excel to indicate that 26 & 29 are the most frequent on each row? The numbers can only appear once on a row and the range is between 1-50...
    Sorry if the explanation is not clear itís hard to put in words.
    I have searched all around but can find a solution, any help from you excel gods would be much appreciated.

    Thank you.....
    Attached Files Attached Files

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Ha...Irish lotto results.

  3. #3
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Here ya go. Very tricky to achieve with formulas. I had to dig deeply into my bag of array formula tricks to pull this one off.

    You can download the file from https://docs.google.com/file/d/0B1hg...it?usp=sharing (it's slighly too big to upload here).

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Note that http://www.lottery.co.uk/statistics/ has a 'most common pairs' section, which is what you probably want in any case. Regardless, this was a fun challenge.

  5. #5
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Something wasn't quite right with previous file. Here's a link to a revised one: https://docs.google.com/file/d/0B1hg...it?usp=sharing

Tags for this Thread

Posting Permissions

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