Forum Discussion
Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.
- Sep 07, 2023
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?
What is returned by dynamic array formula is called spill. Spill is considered as one object, you can't edit inside it.
To copy/paste some cells from the spill we need to paste them as values. For example
Select something within the spill and copy (Ctrl+C) it
Select the target cell and paste into it. Blank cell appears, but you shall see icon with Ctrl on the right.
expand it and select 123 icon which means paste as value. Enter.
Now you have all values in the pasted range
The only, if necessary, is to apply desired format to them (you may use Format Painter).
Alternatively you paste by clicking on ribbon Paste->Paste as value.
I see what in your sample some texts are missing (blank cells) and formula returns zero in such case. To avoid that I change "group" step on
group, SUBSTITUTE( UNIQUE( CHOOSECOLS(monthRange, groupIndexes ) ), "", "" ),
(wrapped by SUBSTITUTE), with that we have empty string instead of zero. Entire formula is in attached file, With ID tab.
- Norman_McIlwaineOct 08, 2023Brass ContributorThank you for the quick and excellent response.
- SergeiBaklanAug 31, 2023MVP
Okay, thank you for the confirmation
- Norman_McIlwaineSep 02, 2023Brass Contributor
The formula is working excellent with one exception the VISA section of my download is giving me an error message #CALC when I execute the formula against the download. I can see when I evaluate the formula the LAMBDA statement is showing an error #VALUE in the amount field. I guess the #CALC is related to this as an empty array. if it is a problem with the download I can always go back to the RBC bank and ask them to fix the problem.
Output
Input Data
Name Manager
Thanks for having a look at this for me.
- SergeiBaklanSep 02, 2023MVP
Okay, in your source data such Id is transforming into number. When we wrap group by SUBSTITUTE it returns back as text. However, Id in filter is still the number. Since number <> text, formula finds nothing and returns an error.
The workaround could be to transform Id into the text the same way, i.e. use
Id, SUBSTITUTE(CHOOSECOLS(monthRange, 1),"",""),
When it shall return correct result. Entire formula will be
=LET( date, CHOOSECOLS(range, 2), rangeId, CHOOSECOLS(range, 1), monthRange, FILTER(range, (EOMONTH(date,0) = EOMONTH( DATE(year,monthNumber,1), 0 ))* IF( monthID = "", 1, ( rangeId = monthID ) ) ), Id, SUBSTITUTE(CHOOSECOLS(monthRange, 1),"",""), transaction, CHOOSECOLS(monthRange, 4), type, CHOOSECOLS(monthRange, 5), amount, CHOOSECOLS(monthRange, 6), groupIndexes, {1,4,5}, group, SUBSTITUTE( UNIQUE( CHOOSECOLS(monthRange, groupIndexes ) ),"",""), result,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) ) ) ) ) ) ) ), result )
which is in With ID sheet attached.
- Norman_McIlwaineSep 02, 2023Brass Contributor
Changed the source Id to text and full Id text. It all looks good now.
Thanks for the help
- Norman_McIlwaineSep 02, 2023Brass Contributor
Just noticed that when all id are selected (id field blank) the formula does not sum properly. i.e. the INTEREST rates in three id's are only summed within the id.
Source data
Output Data
Thanks for looking at this for me, if I can help please ask me.
- SergeiBaklanSep 02, 2023MVP
Sorry, I didn't catch. In your sample Source data for the ID="06962-5001375" and Transaction = "INTEREST" has only one record with amount -2.81. However in Output we have -20.59. Is that full data in your sample?
- Norman_McIlwaineSep 02, 2023Brass Contributor
Sorry I took so long to respond I had some business to attend to around the house.
I had forgotten that I was only looking at data for month 7 that was my mistake. So I selected only month 7 and interest from the full download (286 entries) and this is what I found with the id field empty.
- Norman_McIlwaineSep 02, 2023Brass ContributorBasically what I am looking at when the Id field is empty is the total amount for the Transaction and Type fields. I.e. Total Interest paid for the month and year requested, The id field in this case would be of no concern. However if I still wanted it to be of a concern is when I select an id with the month and year.
- SergeiBaklanSep 04, 2023MVP
I added "Any" in such case into first column
If you don't need it you may use
IF(monthID="", DROP( result,,1), result )instead of result at the end of the formula.First half of the formula is modified for such filtering=LET( date, CHOOSECOLS(range, 2), rangeClean, HSTACK( SUBSTITUTE( DROP(range,,-1), "", "" ), TAKE(range,,-1) ), IdClean, SUBSTITUTE(monthID, "", ""), ifDates, (EOMONTH(date,0) = EOMONTH( DATE(year,monthNumber,1), 0 )), ifId, CHOOSECOLS(rangeClean, 1) = IdClean, monthRange, IF( IdClean = "", IFNA( HSTACK("Any", DROP( FILTER(rangeClean, ifDates ),,1) ), "Any"), FILTER(rangeClean, ifDates*ifId ) ), 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 ) ), result,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) ) ) ) ) ) ) ), result )