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

[Norman_McIlwaine]

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.

[#2]

@Norman_McIlwaine 

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
)

[#3]

@Norman_McIlwaine 

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.

[#4]

@Norman_McIlwaine 

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.

[#5]

@SergeiBaklan 

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.

[#6]

@Norman_McIlwaine 

Please check if references are correct

Based on second screenshot year is in J1, etc

[#7]

@Norman_McIlwaine 

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.

[#8]

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.

[#9]

@Norman_McIlwaine 

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?

[#10]

@SergeiBaklan 

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.

[#11]

@Norman_McIlwaine 

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.

[#12]

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.

[#13]

@Norman_McIlwaine 

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
)

[#14]

@Norman_McIlwaine 

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.

[#15]

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

[#16]

@Norman_McIlwaine 

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?

[#17]

@Norman_McIlwaine 

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.

[#18]

@SergeiBaklan 

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

[#19]

@Norman_McIlwaine 

Okay, thank you for the confirmation

[#20]

@SergeiBaklan 

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.

[#21]

@Norman_McIlwaine 

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.