Forum Discussion
Solved
Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.
I am trying to add 5 additional columns to the function but can get only a $value: Function : =LET(r,B1:G8,m,6,a,TAKE(r,,1),b,INDEX(r,,2),c,INDEX(r,,3),d,INDEX(r,,4),e,INDEX(r,,5),f,INDEX(r,,...
Thank you. Do I understand correctly that csv62719 is you source file. If to avoid Power Query we copy/paste data from it into the sheet in main file, here by formula create massaged range and based on it and month/year/monthid parameters create aggregated result. Workflow is correct?
I found this to be very good on the small amount of data. When I expanded the data over several months (8,4) I found the amount is summed over all months while the output from Id, Transaction, and Type are only selecting by the month requested.
Example PETRO
Corrected, sorry. All to be done on monthRange, nor range.
=LET(
range, $B$2:$G$19,
year, $J$3,
monthNumber, $J$4,
date, CHOOSECOLS(range, 2),
monthRange, FILTER(range, EOMONTH(date,0) = EOMONTH( DATE(year,monthNumber,1), 0 )),
Id, CHOOSECOLS(monthRange, 1),
transaction, CHOOSECOLS(monthRange, 4),
type, CHOOSECOLS(monthRange, 5),
amount, CHOOSECOLS(monthRange, 6),
groupIndexes, {1,4,5},
group, UNIQUE( CHOOSECOLS(monthRange, groupIndexes ) ),
res,VSTACK({"Id","Transaction","Type","Amount"},
HSTACK(
group,
BYROW( group,
LAMBDA(v, SUM(
FILTER(amount,
(Id = INDEX(v,1,1) )*
(transaction = INDEX(v,1,2) )*
(type = INDEX(v,1,3) )
)
) )
)
) ), res
)