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]

@SergeiBaklan 

Changed the source Id to text and full Id text. It all looks good now.

Thanks for the help

[#3]

@Norman_McIlwaine 

Just noticed that when all id are selected (id field blank) the formula does not sum properly. i.e. the INTEREST rates in three id's are only summed within the id.

Source data

Output Data

Thanks for looking at this for me, if I can help please ask me.

[#4]

@Norman_McIlwaine 

Sorry, I didn't catch. In your sample Source data for the ID="06962-5001375" and Transaction  = "INTEREST" has only one record with amount -2.81. However in Output we have -20.59. Is that full data in your sample? 

[#5]

@SergeiBaklan 

Sorry I took so long to respond I had some business to attend to around the house. 

I had forgotten that I was only looking at data for month 7 that was my mistake. So I selected only month 7 and interest from the full download (286 entries) and this is what I found with the id field empty.

[#6]

Basically what I am looking at when the Id field is empty is the total amount for the Transaction and Type fields. I.e. Total Interest paid for the month and year requested, The id field in this case would be of no concern. However if I still wanted it to be of a concern is when I select an id with the month and year.

[#7]

@Norman_McIlwaine 

I added "Any" in such case into first column

If you don't need it you may use 

  IF(monthID="", DROP( result,,1), result )

instead of result at the end of the formula.

First half of the formula is modified for such filtering

=LET(
    date,      CHOOSECOLS(range, 2),

    rangeClean, HSTACK(
                    SUBSTITUTE( DROP(range,,-1), "", "" ),
                    TAKE(range,,-1) ),
    IdClean,    SUBSTITUTE(monthID, "", ""),

    ifDates,    (EOMONTH(date,0) = EOMONTH( DATE(year,monthNumber,1), 0 )),
    ifId,       CHOOSECOLS(rangeClean, 1) = IdClean,
    monthRange,
         IF( IdClean = "",
            IFNA( HSTACK("Any", DROP( FILTER(rangeClean, ifDates ),,1) ), "Any"),
            FILTER(rangeClean, ifDates*ifId )
    ),

    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
)

[#8]

@SergeiBaklan 

You are defiantly the best in knowledge about Excel. The program works beautifully against the download. Now I am trying to massage the download and have completed several steps manually so far. However I have noticed that thee is an empty row in the column beside the one I want to move. I am trying to substitute what is in column H (which is empty) with the corresponding row value in G and I am running into a problem with the functions of SUBSTITUTE and IF working together. That is to say: SUBSTITUTE what is in Column H with the value in column G, IF the value in column H is "" .

Sample download massaged.

Formula

Thanks for all you help.

[#9]

@Norman_McIlwaine 

Sorry, I missed a bit. Do you mean empty cell or empty row; columns E anf F or columns H and G?

Perhaps you may illustrate on small range what do you have initially and what would you like to have after some formula will be applied?

[#10]

Sorry, for the misunderstanding. It is the Cells in column F which are blank that I am trying to populate from the corresponding cell in column G.

Source data

Source Massaged Data

[#11]

@Norman_McIlwaine 

I see, thank you. In general that's job for Power Query if you consider such option. It was created to transform data. If you initially get data from csv file or other source, you only need to say Power Query from which file/folder to take data, the rest could be done within query and loaded into the grid, includes result of the formula we discussed. Once done, the rest of the job to click Refresh All updating with new data.

If with formula, we may take entire data from one place, apply formulae and put result into another place. The question is to you need to edit something additionally within resulting range or not.

If massaged data is needed only to generate in bit another way result of the current formula, it's better to adjust that formula.

To replace data in place it could couple of options - OfficeScript if it is available in your subscription ( if so it shall be Automate tab on ribbon).

Or VBA programming, but that's not my territory.

So, all depends on goals and tools you have in hand. What will be desirable option?

[#12]

Thank you for the informative reading. I will try one of the options.

[#13]

HELP!!!!!

I massaged the download using power query and gat the data to what I would like.

I have also made the changes to the formula for this download. However, when I run it I get the #CALC error.

Where did I go wrong?

Thanks for any advice you can provide me.

[#14]

@Norman_McIlwaine 

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

[#15]

@Norman_McIlwaine 

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.

[#16]

@SergeiBaklan 

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.

[#17]

@Norman_McIlwaine 

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.

[#18]

@SergeiBaklan

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?

[#19 · Accepted solution]

@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?

[#20]

@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.

[#21]

@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.