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?
SergeiBaklan
Aug 18, 2023MVP
- Norman_McIlwaineAug 19, 2023Brass ContributorThank 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.- SergeiBaklanAug 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.