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

CodePudding user response：

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)''"))

---

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, )))

CodePudding user response：

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,