Forum Discussion
Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.
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.
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?
In general your formula
=LET( r, B1:G8, m, 8, 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)) )works, e.g.
but I was not able to paste your sample into the grid and didn't catch what exactly you try to do.
- Thank Sergei for your quick response I am trying to output the following fields B,E,F,G by Month(C), B, E, F, G. The output looks like this:
B E F G
06962-5001375 INSURANCE IND ALL AC-EST -203.64
06962-5001375 INSURANCE THE PERSONAL -469.88
06962-5001375 OD HANDLING FEE 1 @ $ 5.00 -5
06962-5001375 Transfer WWW TRANSFER - 0982 1000.00
DATA
A B C D E F G
1 Chequing 06962-5001375 2023-08-01 " " INSURANCE IND ALL AC-EST -203.64
2 Chequing 06962-5001375 2023-08-01 " " INSURANCE THE PERSONAL -269.88
3 Chequing 06962-5001375 2023-08-01 " " INSURANCE THE PERSONAL -200.00
4 Chequing 06962-5001375 2023-08-02 " " OD HANDLING FEE 1 @ $ 5.00 -5
5 Chequing 06962-5001375 2023-08-09 " " Transfer WWW TRANSFER - 0982 1000.00
6 Chequing 06962-5001375 2023-08-09 " " Payment - 5925 MILTON - TAXE -50
7 Chequing 06962-5001375 2023-08-09 " " Payment - 2317 RELIANCE -55
8 Chequing 06962-5001375 2023-08-09 " " Payment ENT - 8635 MILTON HYDRO -145.52
Chequing 06962-5001367 2023-04-18 OD INTEREST -3.7
i removed www for readability only.
I hope I have got it right this time. I have been trying to get this right for at least two weeks and it is driving me crazy.Sorry, I still can't understand how the source data is structured. Is that as
? If so what is the logic behind generating these 4 output records, why these ones not another? Or your output doesn't cover entire input?
