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]

@Norman_McIlwaine 

I'm not familiar with iPad. If Power Query that's only to refresh. Otherwise to build dynamic array formula from scratch.

[#3]

@SergeiBaklan 

Ok. Thanks for the speedy response. I have been updating my skills and using Power query with Pivot Tables. I am not sure if this is an excel question or a Microsoft question. Every time I open a pivot table for an Excel table, I get four error messages in my event log:

I have tried installing the MSOLAO like it says and still get the same error message. Have you seen a fix for this anywhere?

[#4]

@Norman_McIlwaine 

Is that on Windows?

[#5]

@SergeiBaklan

Yes, it is on windows 10 Pro. 

[#6]

@Norman_McIlwaine 

That's quite old thread Event ID 25 from source MSOLAP$LocalCube (microsoft.com) if something could help from it.

I'd check first if Power Pivot (data model) works on any new file - create simple source table, build PivotTable adding data to data model. If doesn't work perhaps you need to repair the Office. 

[#7]

@SergeiBaklan 

Tried to add data to data and I get the MSOLAP error message. I will now do a repair of Microsoft 365 and see if that will clear the error message.

[#8]

I did the repair and installed the latest Microsoft 365 from online and continue to get the same errors when inserting a PIVOT Table.

So far, I have narrowed down the MSOLAP$LocalCube error to when I select a Table in Excel and use the Insert -Pivot Table command four errors appear immediately in the Event Viewer Log. However, if I continue and create a pivot table then delete the pivot table and I recreate using same commands against the same Excel Table Insert Pivot Table I do not get an error message. If I change my Excel Table to another Table and perform insert Pivot command the error appears.

Is this a bug with EXCEL or PIVOT Table?

Your expertise would be appreciated.