Forum Discussion
Excel formula to count how many times the same 5 names show up with the same date. aka NBA lineup
There are 3 columns a5:500 1-5 repeating every 6th row b5:500 Date which changes every 6th row c5:c500 Player names vary depending on who started. I want find how many times the same 5 play...
Hendew44 This should work (but there may be easier ways):
=LET(roster_list, B2:D31, name_list, G2:G6,
sorted_starters, BYCOL(WRAPCOLS(TAKE(roster_list,,-1),5),LAMBDA(a, TEXTJOIN(",",,SORT(a)))),
sorted_name_list, TEXTJOIN(",",,SORT(name_list)),
SUM(--(sorted_name_list=sorted_starters)))EDIT: for example this is slightly more concise:
=LET(roster_list, B2:D31, name_list, G2:G6,
starters, WRAPROWS(TAKE(roster_list,,-1),5),
SUM(--(MMULT(--ISNUMBER(XMATCH(starters,name_list)),{1;1;1;1;1})=5)))