Finding second (and subsequent) occurrences of matches

[Grumpy88]

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!

 

[bosco_yip]

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

 

[Grumpy88]

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.

 

[bosco_yip]

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

 

[Grumpy88]

That looks absolutely magic! Thank you so much!

 

[Grumpy88]

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.

 

[bosco_yip]

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

 

[Grumpy88]

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!

 

[bosco_yip]

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

 

[Grumpy88]

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.

 

[bosco_yip]

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