SOLVED

# Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

Brass Contributor

# 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,,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

86 Replies

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

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
)``````

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

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.

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

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.

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

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.

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

Please check if references are correct

Based on second screenshot year is in J1, etc

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

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.

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

Changed the range to B2:G289 and it works terrific. An excellent formula thank you. Not sure what you mean by putting definitions in the Define Names. I have perused it in researching the define names however, it just talks about putting cells into a defined name. Also I can not get the editor to work properly ie how do you get a new line in the fields. I would appreciate it if you could help me out on this one.

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

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?

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

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?

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

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.

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

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.

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

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
)``````

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

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.

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

@Norman_McIlwaine , great to know, thank you for the feedback

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

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?

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

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.

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

The output is looking good, thanks for the tweak to the "group"

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

Okay, thank you for the confirmation

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

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.

# Re: Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

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.