Aug 18 2023 01:31 PM
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,,6),g,INDEX(r,7),u,SORT(UNIQUE(a)),HSTACK(u,MMULT(--(TOROW(a)=u),(MONTH(b)=m)*c)))
Data
A B C D E F G
Chequing 06962-5001375 2023-08-01 " " INSURANCE IND ALL AC-EST -203.64
Chequing 06962-5001375 2023-08-01 " " INSURANCE THE PERSONAL -269.88
Chequing 06962-5001375 2023-08-02 " " OD HANDLING FEE 1 @ $ 5.00 -5
Chequing 06962-5001375 2023-08-09 " " Transfer WWW TRANSFER - 0982 1000.00
Chequing 06962-5001375 2023-08-09 " " Payment WWW PAYMENT - 5925 MILTON - TAXE -50
Chequing 06962-5001375 2023-08-09 " " Payment WWW PAYMENT - 2317 RELIANCE -55
Chequing 06962-5001375 2023-08-09 " " Payment WWW PAYMENT - 8635 MILTON HYDRO -145.52
Chequing 06962-5001367 2023-04-18 OD INTEREST -3.7
Result #VALUE
If I use a subscript of the data i.e. 3 col The function is of:
Function:
=LET(r,A1:G8,m,6,a,TAKE(r,,1),b,INDEX(r,,2),c,INDEX(r,,3),u,SORT(UNIQUE(a)),HSTACK(u,MMULT(--(TOROW(a)=u),(MONTH(b)=m)*c)))
Data:
06962-5001375 2013-06-10 2.00
06962-5001375 2013-06-10 3.00
06962-5001375 2013-05-13 4.00
06962-5001375 2013-05-14 5.00
06962-5001375 2013-06-15 6.00
06962-5001380 2013-06-17 7.00
03092-5224710 2013-06-17 8.00
03092-5224710 2013-06-18 9.00
Result:
06962-50011121 3
06962-50011122 3
06962-50011123 6
Thanks tp P. b. at stack overflow for the original suggestion.
How do I add in the extra columns?
I would appreciate any help that you can provide me
Thanking you in advance.
Aug 27 2023 07:40 AM
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
)
Aug 27 2023 05:37 PM
The formula is excellent against the full download 289 rows all worked perfectly . Thank you so much I am sorry, I tried to make a small change to put an if statement "monthId" in to select on the Id however, the if statement works inside the LET but I cannot get the formula to output by the monthId. I have tried to put the monthId variable in several locations and I still get the same output. If I change the "Id=INDEX(v1,1) to show the monthId variable I get error messages.
Once again thanks for your help. I have learned so much from you.
Aug 28 2023 12:35 AM
If like this
when
=LET(
range, $B$2:$G$19,
year, $J$3,
monthNumber, $J$4,
monthID, $J$5,
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, 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
)
If ID is skipped (J5 is empty) it returns all ID for the selected month.
Please check With ID sheet attached.
Aug 28 2023 05:12 AM
When I execute the formula
I get #CALC as my output
I did an evaluate on the formula and see a #VALUE in several places
am I missing something I tried to fix but to no avail.
Aug 28 2023 09:25 AM
Please check if references are correct
Based on second screenshot year is in J1, etc
Aug 28 2023 09:28 AM
In addition, even better if you define above names not in formula but directly in Excel using
Formula->Define Names or Name Manager. With that you may remove these definitions from the formula.
Aug 28 2023 11:42 AM
Aug 28 2023 12:13 PM
Perhaps the easiest way is to define names using Name Box (on top left of the grid). Stay on the cell which you'd like to name, type in the box the name, Enter
Same for the range - select it in grid and enter the name.
If you'd like to edit name reference, that's Formula->Name Manager, here stay on the bottom bar, press F2 and edit here
Formula in 'With ID' sheet is with such names, internal ones are removed.
Didn't catch about new lines, do you mean multiline text within the cell or something else?
Aug 29 2023 07:19 AM
Serge I placed the year, monthID, monthNumber as definitions, when I asked to run in FORMULAS > RUN IN FORMULAS I get the error message:
How can I run the definitions in the formula?
Thanks for your help.
Aug 29 2023 08:19 AM
Now just remove these two in formula
Please be sure the range is correct. In initial formula it was B2:G300, now you defined it as csv62719!A2:H287
Run in Formulas was not necessary.
Aug 29 2023 11:37 AM
I have cleaned up the definitions and when I execute the formula i get a #VALUE in the "group" definition. I have looked at this field several times and cannot see why it would give an error. Can you have a look at it for me Thanks.
Aug 29 2023 12:10 PM
Please do not define here internal names. Only what is in greed - range, month, and monthId. Nothing else. Entire formula with that 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, 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
)
Aug 29 2023 12:19 PM
Problem solved incorrect placing of a comma and result. All fixed now looking good. You are a wonderful person for helping me on this project.
Aug 30 2023 04:22 AM
@Norman_McIlwaine , great to know, thank you for the feedback
Aug 30 2023 03:20 PM
Just a quick question, Why do I have a blue box around all the cells from the output and cannot transfer the values from one cell in the blue box to another cell outside the blue box.? I get a blank cell in the TO cell after I copy and paste. Sorry my blue lines are not straight.
I get the blue box as soon as my cursor enters any cell inside the output range. How can I copy the contents of the cell inside the blue box say to another cell location?
Aug 31 2023 04:27 AM
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.
Aug 31 2023 07:23 AM
Aug 31 2023 07:47 AM
Okay, thank you for the confirmation
Sep 01 2023 07:26 PM
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.
Sep 02 2023 05:15 AM
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.