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)

 

[alansidman]

PQ Refresh.xlsm
	A	B	C	D	E	F	G	H	I
1					Month			January
2	Name	date	pins		March			February
3	fred	3/28/2024	7					March
4	wilma	3/15/2024	5					April
5	wilma	3/17/2024	4					May
6	dino	2/28/2024	3					June
7								July
8								August
9	Name	Total Pins						September
10	wilma	9						October
11	fred	7						November
12								December
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$H$1:$H$12

Load your data as Table1 to Power Query Editor

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"pins", Int64.Type}, {"date", type date}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Changed Type", {{"date", each Date.MonthName(_), type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted Month Name", each ([date] = Table2)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Name"}, {{"Total Pins", each List.Sum([pins]), type nullable number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Total Pins", Order.Descending}})
in
    #"Sorted Rows"

Load a parameter query to the PQE. This is the month lookup using Data Validation

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Month = Source{0}[Month]
in
    Month

You can then refresh the results with each month change by creating a command button with the following VBA

Private Sub CommandButton1_Click()
ActiveWorkbook.RefreshAll
End Sub

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[foranpower]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I failed to save that before. Thanks for the tip. Fluff, it was your solution to a previous problem that got me thinking this may actually be possible.

 

[foranpower]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

2021

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[foranpower]

PQ Refresh.xlsm
	A	B	C	D	E	F	G	H	I
1					Month			January
2	Name	date	pins		March			February
3	fred	3/28/2024	7					March
4	wilma	3/15/2024	5					April
5	wilma	3/17/2024	4					May
6	dino	2/28/2024	3					June
7								July
8								August
9	Name	Total Pins						September
10	wilma	9						October
11	fred	7						November
12								December
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$H$1:$H$12

Load your data as Table1 to Power Query Editor

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"pins", Int64.Type}, {"date", type date}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Changed Type", {{"date", each Date.MonthName(_), type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted Month Name", each ([date] = Table2)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Name"}, {{"Total Pins", each List.Sum([pins]), type nullable number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Total Pins", Order.Descending}})
in
    #"Sorted Rows"

Load a parameter query to the PQE. This is the month lookup using Data Validation

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Month = Source{0}[Month]
in
    Month

You can then refresh the results with each month change by creating a command button with the following VBA

Private Sub CommandButton1_Click()
ActiveWorkbook.RefreshAll
End Sub

That is quite impressive code.
Still trying to figure it all out.

I copied your table, then
-> data
--> from table/range
--->view
---->advanced editor
-----> copy pasted your code
got it working up until table2. when i try to highlight two parts it says can't select multiple areas.

if possible, could it be for a date range between two dates say 1 March - 27 April a variable range.
Not sure if useful thinking: vstack(sum of pins as you put it, by name, filter (between starting on start date to ending on end date)

Just learning about power query looking up videos of how to do it and learning from you. Your patience is appreciated

 

[Fluff]

With a formula

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Name	date	pins		Start date	End date		Name	Total
2	fred	28/03/2024	7		01/03/2024	29/03/2024		wilma	9
3	wilma	15/03/2024	5					fred	7
4	wilma	17/03/2024	4
5	dino	28/02/2024	3
6
Data

Cell Formulas
Range	Formula
H2:I3	H2	=LET(u,UNIQUE(FILTER(A2:A100,(B2:B100>=E2)*(B2:B100<=F2))),SORT(CHOOSE({1,2},u,SUMIFS(C:C,B:B,">="&E2,B:B,"<="&F2,A:A,u)),2,-1))
Dynamic array formulas.

 

[alansidman]

Here is a link to the file I referenced previously. You can then see the steps I suggested and you should be able to follow.

PQ Refresh.xlsm | Powered by Box

app.box.com

Additionally, here is a link to parameter queries tutorial

 

[alansidman]

Here is how I formulated the Mcode to allow for a range of dates.
Build two tables. Start and End. Bring them into the PQ Editor as before and Close and Load to a connection only.

Here is the new Mcode which incorporate those two tables referred to as Table5 and Table6 in the example.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"pins", Int64.Type}, {"date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [date] >= Table5 and [date] <= Table6),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Name"}, {{"Total Pins", each List.Sum([pins]), type nullable number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Name", Order.Ascending}})
in
    #"Sorted Rows"

Here is a new link with both examples.