1

I have a table with names of players like this:

Player
John
Eric
Valerie
Carmen

And another table with a list of played matches (match number, match date and the list of players that played in the match). Something like this:

Match Date Player1 Player2 Player3
1 15/11/2022 John Eric
2 15/11/2022 John Eric
3 15/11/2022 John Eric
4 16/11/2022 John Valerie Carmen
5 16/11/2022 John Carmen
6 17/11/2022 John Carmen

Now with these information I would like to add a column to the player table showing the number of different days each player has played. Something like this:

Player Days (attendance)
John 3
Eric 1
Valerie 1
Carmen 2

How can I do this?

My idea was:

  • foreach player, select all records from the matches tables containing the player. For example with player Carmen I will select these:
Match Date Player1 Player2 Player3
4 16/11/2022 John Valerie Carmen
5 16/11/2022 John Carmen
6 17/11/2022 John Carmen
  • from these records consider only the column date and and the column current player
Date Player
16/11/2022 Carmen
16/11/2022 Carmen
17/11/2022 Carmen
  • remove duplicates
Date Player
16/11/2022 Carmen
17/11/2022 Carmen
  • And finally count the number of elements

This was my idea but I'm a novice and I have not been able to implement it. How can I do this (or something similar)? Thanks!!

player0
  • 124,011
  • 12
  • 67
  • 124
G. Lari
  • 435
  • 2
  • 14

2 Answers2

2

try:

=INDEX(QUERY(SPLIT(UNIQUE(FLATTEN(IF(C2:E="",,B2:B&"​"&C2:E))), "​"), 
 "select Col2,count(Col2) where Col2 is not null group by Col2 label count(Col2)''"))

enter image description here

update:

=INDEX(IFNA(VLOOKUP(<column of names here>, 
 QUERY(SPLIT(UNIQUE(FLATTEN(IF(C2:E="",,B2:B&"​"&C2:E))), "​"), 
 "select Col2,count(Col2) where Col2 is not null group by Col2 label count(Col2)''"), 2, )))
player0
  • 124,011
  • 12
  • 67
  • 124
  • Thank you Player0! I tried your code (without being able to understand it yet) and it generate a couple of columns: the players and relative attendance days. The value in the columns are correct (great!!) but I already have the list of players and I can't rewrite it. The code I need should write next each player (in the column beside) , the number of days. Or alternatively, how can I copy your results in my table according each player? – G. Lari Nov 17 '22 at 12:40
  • it looks there is one closing bracket at the end that should not be there. If I remove it I get the error "Wrong number of arguments to IFNA. Expected between 1 and 2 arguments, but received 3 arguments." – G. Lari Nov 17 '22 at 13:46
  • @G.Lari fixed, try now – player0 Nov 17 '22 at 13:56
0

You can try this:

players['Days_Attendance'] = [list(matches['Player 1']).count(e) + 
list(matches['Player 2']).count(e) + list(matches['Player 2']).count(e) for e in 
players['Player']]

I do not know if you have a lot of columns "Planer n". If that is the case, you could think in create a function with the pourpouse of have a clean script.

Or, more complex for 12 players:

players['Days_Attendance'] = [sum(list(matches['Player ' + str(n)]).count(e) for 
n in range(1,12)) for e in players['Player']]

Regards,

Gabino Antuña Ortiz
  • 3
  • 4
  • I'm sorry to ask but I have no idea where I should enter your code. I just started using google sheet. – G. Lari Nov 17 '22 at 12:42