Forum Discussion

Norman_McIlwaine's avatar
Norman_McIlwaine
Brass Contributor
Aug 18, 2023

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

 

Thanking you in advance.

  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 07, 2023

    Norman_McIlwaine 

    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?

  • Norman_McIlwaine 

    In general your formula

    =LET(
        r, B1:G8,
        m, 8,
        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))
    )

    works, e.g.

    but I was not able to paste your sample into the grid and didn't catch what exactly you try to do.

    • Norman_McIlwaine's avatar
      Norman_McIlwaine
      Brass Contributor
      Thank Sergei for your quick response I am trying to output the following fields B,E,F,G by Month(C), B, E, F, G. The output looks like this:

      B E F G
      06962-5001375 INSURANCE IND ALL AC-EST -203.64
      06962-5001375 INSURANCE THE PERSONAL -469.88
      06962-5001375 OD HANDLING FEE 1 @ $ 5.00 -5
      06962-5001375 Transfer WWW TRANSFER - 0982 1000.00

      DATA
      A B C D E F G
      1 Chequing 06962-5001375 2023-08-01 " " INSURANCE IND ALL AC-EST -203.64
      2 Chequing 06962-5001375 2023-08-01 " " INSURANCE THE PERSONAL -269.88
      3 Chequing 06962-5001375 2023-08-01 " " INSURANCE THE PERSONAL -200.00
      4 Chequing 06962-5001375 2023-08-02 " " OD HANDLING FEE 1 @ $ 5.00 -5
      5 Chequing 06962-5001375 2023-08-09 " " Transfer WWW TRANSFER - 0982 1000.00
      6 Chequing 06962-5001375 2023-08-09 " " Payment - 5925 MILTON - TAXE -50
      7 Chequing 06962-5001375 2023-08-09 " " Payment - 2317 RELIANCE -55
      8 Chequing 06962-5001375 2023-08-09 " " Payment ENT - 8635 MILTON HYDRO -145.52
      Chequing 06962-5001367 2023-04-18 OD INTEREST -3.7

      i removed www for readability only.

      I hope I have got it right this time. I have been trying to get this right for at least two weeks and it is driving me crazy.
      • SergeiBaklan's avatar
        SergeiBaklan
        MVP

        Norman_McIlwaine 

        Sorry, I still can't understand how the source data is structured. Is that as

        ? If so what is the logic behind generating these 4 output records, why these ones not another? Or your output doesn't cover entire input?

Share

Resources