SOLVED

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 :

 

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

86 Replies

@Norman_McIlwaine 

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:

 

Norman_McIlwaine_0-1701104545461.png

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

 

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

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