1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding second (and subsequent) occurrences of matches

Discussion in 'Ask an Excel Question' started by Grumpy88, Jul 11, 2018.

  1. Grumpy88

    Grumpy88 Member

    Messages:
    146
    Hi.

    Please see the attached sample from a much larger Excel database that I am running.

    I need to extract all of the teams that each player has appeared for (as compiled from the Appearances tab), and list them in columns B - G on the Summary tab.

    I can determine the first occurrence easily enough with an INDEX / MATCH formula combination, but I don't know how to adapt the formula to list the second applicable team for each player in column C, the third in column D, etc.

    Can anyone perhaps help?

    Thanks so much!

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,909
    Try,

    In "Summary" sheet copied across right and down :

    =IFERROR(INDEX(Appearances!$B$1:$B$3000,AGGREGATE(15,6,ROW(Appearances!$B$1:$B$3000)/(Appearances!$A$1:$A$3000=$A2),COLUMNS($A:A))),"")

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose likes this.
  3. Grumpy88

    Grumpy88 Member

    Messages:
    146
    Thank you very much, Bosco. Much appreciated!

    At the risk of sounding greedy though, is there a way to alter the returns so that they don't list the team played for in a player's every appearance, but rather just list of all the different teams represented in each player's range of appearances (in the full database, some players have more than 200 appearances in total, so that would be a LOT of columns)?

    In other words, if a given player appears six different times on the Appearances tab, but all for the same team, only that one team should be indicated in column B next to his name. However, if he appears say five times and represents three different teams in those appearances, then those three teams involved are listed (in columns B - D) after his name.
  4. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,909
    To remove duplicate, in "Summary" sheet B2, copied across right and down :

    =IFERROR(INDEX(Appearances!$B$1:$B$3000,AGGREGATE(15,6,ROW(Appearances!$B$1:$B$3000)/(Appearances!$A$1:$A$3000=$A2)/ISNA(MATCH(Appearances!$B$1:$B$3000,$A2:A2,0)),COLUMNS($A:A))),"")

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose and Grumpy88 like this.
  5. Grumpy88

    Grumpy88 Member

    Messages:
    146
    That looks absolutely magic! Thank you so much!
  6. Grumpy88

    Grumpy88 Member

    Messages:
    146
    Sorry to be a pain, but there appears to be an additional complication.

    In my actual database, there are seven other columns between the player name (column A in the uploaded sample) and the first column for holding the team name (column B here). As a result, the last bit of your formula ISNA(MATCH(Appearances!$B$1:$B$3000,$A2:A2,0)),COLUMNS($A:A) doesn't work for the second and subsequent team name column. In the actual database, the player names are in column A, but the first listing of the relevant teams is only column I.

    How do I therefore adapt your formula to ignore the intervening columns B - H? Changing it to ISNA(MATCH(Appearances!$B$1:$B$3000,$A2:I2,0)),COLUMNS($A:I) is not working when dragged across to column J and further for the other team listings.

    Thanks again.
  7. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,909
    Upload a file with 10 lines of data and let me see how the formula made modification.

    Regards
    Bosco
  8. Grumpy88

    Grumpy88 Member

    Messages:
    146
    Thanks.

    I was worried that ten lines might not be enough data to produce the required results, so the attached file is somewhat larger (but not nearly as big as the original one posted).

    Thanks for your time!

    Attached Files:

  9. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,909
    A bit modification,

    In "Summary" sheet I2, copied across right and down :

    =IF($A2="","",IFERROR(INDEX(Appearances!$B$1:$B$3000,AGGREGATE(15,6,ROW(Appearances!$B$1:$B$3000)/(Appearances!$A$1:$A$3000=$A2)/ISNA(MATCH(Appearances!$B$1:$B$3000,$H2:H2,0)),COLUMNS($A:A))),""))

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose likes this.
  10. Grumpy88

    Grumpy88 Member

    Messages:
    146
    Hi Bosco.

    Apologies for not thanking you yet for your latest formula update, but I've been playing around with it in the interim and have noticed that it's not yet performing 100% accurately.

    For the most part it's pretty darn good, but for some reason there seems to be exceptions slipping through. Take for instance the players "Maron, Ryan" and "Roach, Sean". The former has played for four different teams, but only three are detected, while the latter has only one of his two teams identified. There may be others. I have sorted the players on the Appearances tab into alphabetical order on the attached worksheet to better indicate this.

    If you have the time and inclination, would you please see if you can find the glitch in the formula?

    Thanks so much.

    Attached Files:

  11. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,909
    1] Don't use whole column references such as A:A or B:B in your formula, it is 1 million cells in each column.

    2] Formulas that reference whole columns could cause poor performance and hanging

    3] Try to use limited columns range as per my formula in Post #.4

    Regards
    Bosco

Share This Page