Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

Brass Contributor

I am trying to add 5 additional columns to the function but can get only a $value:


Function :




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:








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


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.

86 Replies


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

@Sergei Baklan 


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?



That's quite old thread Event ID 25 from source MSOLAP$LocalCube ( 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. 

@Sergei Baklan 


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.

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.