Forum Discussion
Norman_McIlwaine
Aug 18, 2023Brass Contributor
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,,...
- Sep 07, 2023
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?
Norman_McIlwaine
Aug 19, 2023Brass Contributor
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.
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.
SergeiBaklan
Aug 19, 2023MVP
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?
- Norman_McIlwaineAug 20, 2023Brass ContributorChequing 06962-5001375 2023-04-18 PETRO-CANADA IDP PURCHASE - 4189 -29.42
Chequing 06962-5001375 2023-04-18 C-IDP PURCHASE-3770 NOFRILLS JOHN'S -33.8
Chequing 06962-5001375 2023-04-18 OD INTEREST -0.92
Chequing 06962-5001375 2023-04-19 C-IDP PURCHASE-2812 LUNCHBOX CAFE -2.95
Chequing 06962-5001375 2023-04-19 C-IDP PURCHASE-9840 NOFRILLS JOHN'S -4.58
Chequing 06962-5001375 2023-04-19 C-IDP PURCHASE-9364 FOOD BASICS 88 -44.36
Sergei Sorry for all the confusion, I am trying to have the columns B, C ,E, F, G Displayed by month, by E, by F, by G.
the formula does this but with the by month and by B, E, F, G only if I change the UNIQUE and TOROW statements to the applicable TAKE variable. i.e
=LET(r,B1:G6,
m,4,
x,E1:E6,
a,TAKE(r,,1),
b,INDEX(r,,2),
c,INDEX(r,,3),
d,INDEX(r,,4),
e,INDEX(r,,5),
f,INDEX(r,,6),
v,VLOOKUP(x,x,1,FALSE),
u,SORT(UNIQUE(v)),
HSTACK(u,MMULT(--(TOROW(v)=u),(MONTH(b)=m)*f)))
OUTPUT
C-IDP PURCHASE-2812 -2.95
C-IDP PURCHASE-3770 -33.8
C-IDP PURCHASE-9364 -44.36
C-IDP PURCHASE-9840 -4.58
OD INTEREST -0.92
PETRO-CANADA -29.42
if I change the UNIQUE to "a" and TOROW to "a" my output changes to:
06962-5001375 -116.03
is this the right way to look at the data or can you suggest something else. I am open for any suggestions as I have spent far to many hours on this subject.- SergeiBaklanAug 21, 2023MVP
To add screenshot here, copy it into clipboard, here click on Insert Picture button
after that click by mouse at any place in grey area
Ctrl+V, picture shall be loaded from the clipboard, when it's ready click Done at bottom right.
Back to the sample, I added headers for better understanding, in general you may ignore them.
I guess we need to sum amount for identical Id, Date, Transaction and Type. That could be done by
=LET( range, $B$2:$G$10, Id, CHOOSECOLS(range, 1), date, CHOOSECOLS(range, 2), transaction, CHOOSECOLS(range, 4), type, CHOOSECOLS(range, 5), amount, CHOOSECOLS(range, 6), groupIndexes, {1,2,4,5}, group, UNIQUE( CHOOSECOLS(range, groupIndexes ) ), VSTACK({"Id","Date","Transaction","Type","Amount"}, HSTACK( group, BYROW( group, LAMBDA(v, SUM( FILTER(amount, (Id = INDEX(v,1,1) )* (date = INDEX(v,1,2) )* (transaction = INDEX(v,1,3) )* (type = INDEX(v,1,4) ) ) ) ) ) ) ) )
If we would like to filter for the specific year and month as they are defined here
slightly modified formula will be
=LET( range, $B$2:$G$10, year, $J$14, monthNumber, $J$15, Id, CHOOSECOLS(range, 1), date, CHOOSECOLS(range, 2), transaction, CHOOSECOLS(range, 4), type, CHOOSECOLS(range, 5), amount, CHOOSECOLS(range, 6), monthRange, FILTER(range, EOMONTH(date,0) = EOMONTH( DATE(year,monthNumber,1), 0 )), groupIndexes, {1,2,4,5}, group, UNIQUE( CHOOSECOLS(monthRange, groupIndexes ) ), VSTACK({"Id","Date","Transaction","Type","Amount"}, HSTACK( group, BYROW( group, LAMBDA(v, SUM( FILTER(amount, (Id = INDEX(v,1,1) )* (date = INDEX(v,1,2) )* (transaction = INDEX(v,1,3) )* (type = INDEX(v,1,4) ) ) ) ) ) ) ) )
Your initial formula sum amounts for the unique Id (only one column) in a given month. It's can't be adjusted directly for the few unique columns.
Another way is to create PivotTable, something like
Please check in attached file if that close to what you'd like to receive.
- Norman_McIlwaineAug 22, 2023Brass ContributorThank you Sergei for the most impressive work you have done for me. In particular I like the slightly modified formula. I never thought to have year & month columns but it is very functional. As you can see I am new to EXCEL and willing to learn about various formulas and exciting things you can do with this program. For now I will have to finish my personal finances. Your work has surly helped me a long way. Just one thing I have to ask you, will i be allowed to copy this code from you and run it without typing it all out in case I make a mistake. I can cut and past it from the document however how do I run it excel do I have to create a macro or use VBA which i absolutely no nothing about either?
Once again thank you for your kind help. It has been a long three weeks since I started this project.
- Norman_McIlwaineAug 20, 2023Brass Contributor
This is the entire Source data, I need to have the data structured so that I can have B, F, G By G
i.e.
Output
06962-5001375 INSURANCE IND ALL AC-EST -203.64
06962-5001376 INSURANCE THE PERSONAL -469.88
06962-5001377 OD HANDLING FEE 1 @ $5 5
..
..
..
..
06962-5001367 OD HANDLING INTEREST 3.7
Sorry for the confusion my snip & sketch does not want to paste the snip. However, the above is what my output should look like.
Thanks for the support and patience in helping me resolve this issue.