create column of unique values in date range possibly vstack

[foranpower]

based on this thread, vstack seems to be able to create a column of unique values in a date range:

VSTACK values based on a selected date range

On sheet A I have columns containing names which are created using SPILL formulas. The header for each column is a date. 2/5/23 2/12/23 2/19/23 2/26/23 3/5/23 Petr Blincko Avram Lukas Erinna Fossord Juana Cater Forrest Spencers Myrle Cossar Ashlen Tremonte...

www.mrexcel.com

The goal is highest total value and the champion of the person with the highest sum in the date range. So whoever knocks down the most pins during the competition.

name	date	pins		start date	end date		stacked unique names	total pins
fred	3/28/2024​	7		3/1/2024	3/29/2024		wilma (if possible first named based on having the most bowling pins knocked down)	9 = (5+4)
wilma	3/15/2024​	5					fred	7
wilma	3/7/2024​	4
dino	2/28/2024​	3

Here Wilma would win because during March she had the most pins kocked down, and dino would not show up because he did not bowl during the month.

Tried this formula based on the other post, but get a #name error.
=TOCOL(IF((B2:B5>=E2)*(B2:B5<=F2)*(A2:A5<>""),A2:A5,1/0),3,1)

 

[foranpower]

Thank you Fluffy and Alansidman.


Fluffy, your method works so simply and geniusly. Not sure how you did it.


Alan, I am looking forward to testing your work because it is teaching me a lot about power query methods that I would not have guessed.

Sincerely,
Forest

 

[Fluff]

Glad we could help & thanks for the feedback.