I am trying to figure out if there is a way to have a "table" automatically generate for me using the new dynamic array functions. I put table in quotes because I know it won't be an Excel table as dynamic array functions don't work in them.
I have a table (real table) with 4 columns:
I have the following function that creates a column of vendor IDs that dynamically expands/contracts based on the date, values in the table, etc.
So as long as the Vendor field isn't blank, and the Date is <= the current month end cell, it creates a list of unique vendor IDs sorted alphabetically. Simple enough.
But now I want a second column that also summarizes the amounts by vendor. I can add the 2nd column to the first argument of the FILTER() function, but then the UNIQUE() function looks at the uniqueness of the vendor+amount, which is pretty much always unique.
What I want is a GROUPBY() type function.
I can do this manually with SUMIFS() but the problem there is when the FILTER() function grows/contracts, the SUMIFS() doesn't.
Any ideas on how to accomplish this? If dynamic array functions worked in tables, this would be a snap. I am ok with a 2nd function in the 2nd column, or a single function that returned 2 columns, but I cannot get anything that works.
I have a table (real table) with 4 columns:
- Date
- Vendor
- Amount
- Comment
I have the following function that creates a column of vendor IDs that dynamically expands/contracts based on the date, values in the table, etc.
Code:
=SORT(
UNIQUE(
FILTER(tblRoyaltyAccrual[Vendor],(tblRoyaltyAccrual[Vendor]<>"")*(tblRoyaltyAccrual[Date]<=dtCurrentMonthEnd)
)
)
)So as long as the Vendor field isn't blank, and the Date is <= the current month end cell, it creates a list of unique vendor IDs sorted alphabetically. Simple enough.
But now I want a second column that also summarizes the amounts by vendor. I can add the 2nd column to the first argument of the FILTER() function, but then the UNIQUE() function looks at the uniqueness of the vendor+amount, which is pretty much always unique.
What I want is a GROUPBY() type function.
I can do this manually with SUMIFS() but the problem there is when the FILTER() function grows/contracts, the SUMIFS() doesn't.
Any ideas on how to accomplish this? If dynamic array functions worked in tables, this would be a snap. I am ok with a 2nd function in the 2nd column, or a single function that returned 2 columns, but I cannot get anything that works.
