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,,...
excel
  • 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's avatar
    Norman_McIlwaine
    Brass Contributor
    Aug 19, 2023
    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
      Aug 19, 2023

      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?

      • Norman_McIlwaine's avatar
        Norman_McIlwaine
        Brass Contributor
        Aug 20, 2023

        SergeiBaklan 

        This is the entire Source data, I need to have the data structured so that I can have B, F, G By G
        i.e.
        Output
        06962-5001375 INSURANCE IND ALL AC-EST -203.64
        06962-5001376 INSURANCE THE PERSONAL -469.88
        06962-5001377 OD HANDLING FEE 1 @ $5 5
        ..
        ..
        ..
        ..
        06962-5001367 OD HANDLING INTEREST 3.7


        Sorry for the confusion my snip & sketch does not want to paste the snip. However, the above is what my output should look like.
        Thanks for the support and patience in helping me resolve this issue.

Resources