Forum Discussion

Norman_McIlwaine's avatar
Norman_McIlwaine
Brass Contributor
Aug 18, 2023
Solved

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
Sep 08, 2023

SergeiBaklan 

Thank you so much for your support and help. I have made the necessary updates to the formula to accept the new massaged download. it took a while and I now have what is an acceptable output to assist me in performing my own finances. I am including the file for your perusal if you think I could make any other changes please let me know. In the meantime, I am going to try Power Query to assist me in massaging the download prior to using it with your formula. 

 

Once again thank you for your help, you surly are the best on this subject.

SergeiBaklan's avatar
SergeiBaklan
MVP
Sep 08, 2023

Norman_McIlwaine 

Okay, thank you. Let agree first how you'd like to massage the source data. I'll add the formula after that. Draft done by Power Query is attached.

In first sheet is filePath for your csv file

You need to change it on your actual one and click Refresh All. Result is in second sheet.

If you prefer to keep your file on OneDrive or SharePoint query shall be adjusted a bit. Just inform is that the case.

Another point, perhaps you have the set of csv files. If so not necessary to copy/paste information from each of them. You may create folder (again, on local drive or OneDrive, that's important to know), put each new csv into that folder and Power Query could automatically combine and transform them. If that's and option it's important to know are transactions in such files overlapping or not.

But let eat elephant by a bit. First please check attached and clarify with possible options regarding the data source.

 

Resources