Forum Discussion
Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.
- Sep 07, 2023
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?
I have managed to get some output, however it would appear that I have generated another column and I cannot find were it is from in the formula..
Could you please add your expertise to this matter
Thanks
Sorry, I'm bit busy now and skipped your latest posts. That's great you that Power Query works with you. I guess in formula we shall use now not range name, but reference on the table. At the same time, if Power Query works why do we need formula? We may return the same result with Power Query, that could be easier.
I could help, but that will be much easier if you could share sample file removing sensitive information. If you are not able to attach it to the post you may sent me Private Message here with attached file or put it on OneDrive, whatever, and share the link.
- Norman_McIlwaineOct 08, 2023Brass ContributorThank you for the quick and excellent response.
- Norman_McIlwaineSep 06, 2023Brass Contributor
I have learned so much from you and your formula that I wish to keep it as I am not that comfortable using power query. I have never used it before and just picked it up through a couple of videos that I watched over the past few days. I am not that sure with the formulas contained it the power Query, but will have to pick it up at a fast pace. Beside that most of the cleanup of the download was done manually with a little help from power query.
In the meantime how do you make reference to the data in the formula to delete the last column which contains a lot of #CALC errors in every row. I have tried to make the appropriate changes and moved things around a bit but that's about all my knowledge of Excel. What I have learned is from you and I am very grateful for the experience thank you again.
- SergeiBaklanSep 07, 2023MVP
Okay, I see. Could you please share what is the name of table returned by Power Query
If to stay on any cell within the table you may see it here
I'll try to adjust formula when.
- Norman_McIlwaineSep 07, 2023Brass Contributor
Sorry for the delay I was trying to get the documents to uploaded to you. I really do not like Power Query as it appears to have a lot of limitations that Excel has got. Can we stick with the excel formula?
- SergeiBaklanSep 07, 2023MVP
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_McIlwaineSep 08, 2023Brass Contributor
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.
- SergeiBaklanSep 08, 2023MVP
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.
- SergeiBaklanSep 08, 2023MVP
Preliminary I added formula into Report sheet.
- Norman_McIlwaineSep 09, 2023Brass Contributor
Thank you for looking at this for me, It does look good however I have noticed a few of the items that I require are not in the output and should have been moved from columns 5 to 6.
PENSON,
OLD AGE SECURITY
CPP,
PROV/LOCAL GOV PYMT,
ESSO CIRCLE K
OD INTERESTWhat I tries to do in Power Query was to remove all the entries that contain "IDP" in both columns by using the find and replace function. But I could never use a wild card like *IDP* to remove all the entries. Then I can move the above entries to the corresponding columns without any fear of losing important data. Then I can remove the columns that I do not require.
How to do this in Power Query I do not know. could you send me the Power Query inputs to achieve this output for my perusal then I might understand how power query works. I will send you the latest download to use. - SergeiBaklanSep 09, 2023MVP
Tried to correct, please check in attached if I understood the logic correctly.
Query is within the file. On ribbon Data->Queries and Connections. Double click on right pane on query name to open it in Power Query Editor. Within it you may use Home->Advanced Editor to play with generated M-code.