• 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...

  • 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

Grumpy88

Member
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!
 

Attachments

  • Database Sample.xlsx
    159.2 KB · Views: 4
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
 

Attachments

  • Database Sample(1).xlsx
    558.9 KB · Views: 4
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.
 
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.

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
 

Attachments

  • Database Sample(1A).xlsx
    195.2 KB · Views: 6
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.
 
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.

Upload a file with 10 lines of data and let me see how the formula made modification.

Regards
Bosco
 
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!
 

Attachments

  • Database Sample(1A).xlsx
    319.9 KB · Views: 1
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!
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
 

Attachments

  • Database Sample(1B).xlsx
    321.7 KB · Views: 7
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.
 

Attachments

  • Database Sample(1A).xlsx
    285.5 KB · Views: 2
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.

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
 
Back
Top