Complicated formula

[Bartjeuh78]

Hi there all.
I have a question for the experts here. I hope I can upload the example sheet to make it more clear. I hope it works when openend in an English version of excel. My version is Dutch with Office 365. The yellow data set D4 to R12 is a working formula. I did not made this formula, it is way too complex for me to understand this formula. But now I want this formula somewhat modified. I need 3 extra columns to add to the formula. How it should look is in cells D16 to U24. The yellow is the original formula data, the green is what needs to be added. The working formula gets the data from every month-sheet January to December from cells L6 to M15. The more detailled formula must get the info from cells D6 to H15. The data is exactly the same, just 3 more columns. Who can build this formula? I want it in a formula like the first data set, not a pivot table, and no power query.
Who can help me with this? Thx in advance.
Bart

Laatste Update Budgetfile Ingekort.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2	This is the working forumula for dataset L6 to M15
3	Name	January	February	March	April	May	June	July	August	September	Oktober	November	December	Tot. Year	Avg / Month
4	Autoverzekering				$ 400.00									$ 400.00	$ 400.00
5	Fitness	$ 50.00	$ 50.00	$ 50.00	$ 50.00									$ 200.00	$ 50.00
6	GSM	$ 30.00	$ 30.00	$ 30.00	$ 30.00									$ 120.00	$ 30.00
7	Inboedelverzekering	$ 400.00												$ 400.00	$ 400.00
8	Lidgeld postzegel club	$ 25.00												$ 25.00	$ 25.00
9	Modelbouwclub		$ 40.00											$ 40.00	$ 40.00
10	Netflix			$ 25.00	$ 25.00									$ 50.00	$ 25.00
11	Spotify			$ 20.00	$ 20.00									$ 40.00	$ 20.00
12	TV	$ 40.00	$ 40.00	$ 40.00	$ 40.00									$ 160.00	$ 40.00
13
14	Now I need a formula where 3 extra colums are added. The dataset is D6 to H15 for every month. Fist sorted on column D, then column E
15	Sort	Name	Periodicidy	Info field	January	February	March	April	May	June	July	August	September	Oktober	November	December	Tot. Year	Avg / Month
16	ABONNEMENT	Fitness	MAANDELIJKS		$ 50.00	$ 50.00	$ 50.00	$ 50.00									$ 200.00	$ 50.00
17	ABONNEMENT	GSM	MAANDELIJKS		$ 30.00	$ 30.00	$ 30.00	$ 30.00									$ 120.00	$ 30.00
18	ABONNEMENT	Netflix	MAANDELIJKS				$ 25.00	$ 25.00									$ 50.00	$ 25.00
19	ABONNEMENT	Spotify	MAANDELIJKS				$ 20.00	$ 20.00									$ 40.00	$ 20.00
20	ABONNEMENT	TV	MAANDELIJKS		$ 40.00	$ 40.00	$ 40.00	$ 40.00									$ 160.00	$ 40.00
21	LIDGELD	Lidgeld postzegel club	JAARLIJKS		$ 25.00												$ 25.00	$ 25.00
22	LIDGELD	Modelbouwclub	JAARLIJKS			$ 40.00											$ 40.00	$ 40.00
23	VERZEKERING	Autoverzekering	JAARLIJKS	Polis nr 4321				$ 400.00									$ 400.00	$ 400.00
24	VERZEKERING	Inboedelverzekering	JAARLIJKS	Polis nr 12345	$ 400.00												$ 400.00	$ 400.00
25
Year Totals

Cell Formulas
Range	Formula
D4:R12	D4	=IFERROR(LET(x,VSTACK(January:DECEMBER!L6:M15),y,FILTER(x,(INDEX(x,,1)<>"")*(INDEX(x,,1)<>"selecteer")),u,UNIQUE(INDEX(y,,1)),jan,INDEX(January!M6:M15,MATCH(u,January!L6:L15,0)),feb,INDEX(February!M6:M15,MATCH(u,February!L6:L15,0)),mrt,INDEX(March!M6:M15,MATCH(u,March!L6:L15,0)),apr,INDEX(APRIL!M6:M15,MATCH(u,APRIL!L6:L15,0)),mei,INDEX(May!M6:M15,MATCH(u,May!L6:L15,0)),jun,INDEX(June!M6:M15,MATCH(u,June!L6:L15,0)),jul,INDEX(July!M6:M15,MATCH(u,July!L6:L15,0)),aug,INDEX(August!M6:M15,MATCH(u,August!L6:L15,0)),sep,INDEX(SEPTEMBER!M6:M15,MATCH(u,SEPTEMBER!L6:L15,0)),okt,INDEX(October!M6:M15,MATCH(u,October!L6:L15,0)),nov,INDEX(NOVEMBER!M6:M15,MATCH(u,NOVEMBER!L6:L15,0)),dec,INDEX(DECEMBER!M6:M15,MATCH(u,DECEMBER!L6:L15,0)),z,INDEX(y,,2),s,MMULT(--(TOROW(INDEX(y,,1))=u),INDEX(y,,2)),r,SORT(HSTACK(u,jan,feb,mrt,apr,mei,jun,jul,aug,sep,okt,nov,dec,s),1),HSTACK(r,INDEX(r,,14)/INDEX(GROUPBY(y,y,COUNTA,0,0),,3))),"")
Dynamic array formulas.

 

[Rijnsent]

Hi Bart,
welcome to the forum. That is indeed quite a complex LET formula you've got there. My first idea would be to re-order your data and solve it with simpler step-by-step formulas, but given your challenge, what does your data look like? Your formula works with the 12 sheets of the different months, but can you post a small sample of what that data looks like? From your formula I get that column L and M are being used for the formula, but I don't know where the other desired columns (the green ones in your example) comes from.
Cheers,
Koen

 

[kvsrinivasamurthy]

It is better to Upload a sample file with few sheets and explain how results are achieved.

 

[Bartjeuh78]

Hi Bart,
welcome to the forum. That is indeed quite a complex LET formula you've got there. My first idea would be to re-order your data and solve it with simpler step-by-step formulas, but given your challenge, what does your data look like? Your formula works with the 12 sheets of the different months, but can you post a small sample of what that data looks like? From your formula I get that column L and M are being used for the formula, but I don't know where the other desired columns (the green ones in your example) comes from.
Cheers,
Koen

Hi Koen,
If I look at your name, I guess you speak the same language like me. I struggled to upload the mini excel file. There is no option Here to upload a full excel file. Can I share it with you in another way? Maybe that is more easy. Thx in advance.

 

[Rijnsent]

Hi Bart, I guess your guess is right (ja klopt, maar ik houd t hier in het Engels, er is een aparte groep voor vragen in andere talen). There is indeed no solution to upload the file, what you could do: use a dropbox/google drive/etc link or take e.g. the first 10 lines of your January sheet and use XL2BB to place them here. If possible, try to anonymize the data.
Sending you my email address is unfortunately against forum policy - but you have my full name if you want to try.

 

[Bartjeuh78]

Hi Koen, I'm going to keep it in English here so other community members could also follow this. The idea "create a dropbox link" was something I forgot. I hope the link works. I did already work on it, but I cannot make it work. In the tabs January to December I have 2 data sets. The "original is from L6 to M15. The calculation is done in the tab "Jaartotaal". The formula in D2 works. The formula in D13 is almost the same en works too. The formula in D44 works too, and it is this formula I need to change. What it needs to be is in cells D27 to U41. The blue is what is shown in cells D44 to R52. The green is what needs to be added to the formula. In short: The yellow is the original formula. And I need to add the columns "SOORT", "PERIODICITEIT" and "NOTA" to the formula. The data is in the month tabs cells D6 to H15. I hope the file is more easy to read then the words I type here. If you need additional info, let me know. I hope someone can help me with this.

Dropbox

www.dropbox.com

 

[djclements]

Please try the following:

=LET(
    rng, JANUARI:DECEMBER!D6:H15,
    arr, VSTACK(rng),
    n, SHEETS(rng),
    c, TOCOL(IF(SEQUENCE(,ROWS(arr)/n),SEQUENCE(n))),
    r, CHOOSECOLS(arr,1,2,3,5)&"",
    v, CHOOSECOLS(arr,4),
    i, TAKE(arr,,1),
    p, PIVOTBY(r,c,v,SUM,,0,,1,,i<>"SELECTEER"),
    a, FILTER(p,TAKE(p,,1)<>""),
    HSTACK(a,TAKE(a,,-1)/TAKE(GROUPBY(r,v,ROWS,,0,,(i<>"SELECTEER")*(i<>"")),,-1))
)

You could also try using the same concept to replace your original formula as follows:

=LET(
    rng, JANUARI:DECEMBER!L6:M15,
    arr, VSTACK(rng),
    n, SHEETS(rng),
    c, TOCOL(IF(SEQUENCE(,ROWS(arr)/n),SEQUENCE(n))),
    r, TAKE(arr,,1),
    v, DROP(arr,,1),
    p, PIVOTBY(r,c,v,SUM,,0,,1,,r<>"SELECTEER"),
    a, FILTER(p,TAKE(p,,1)<>""),
    HSTACK(a,TAKE(a,,-1)/TAKE(GROUPBY(r,v,ROWS,,0,,(r<>"SELECTEER")*(r<>"")),,-1))
)

Please note, my regional and language settings are US-English. You may have to translate all functions and replace all commas with semi-colons (sorry).

 

[Rijnsent]

Nice one! I see that the new functions offer way more possibilities than I thought . Note: when I'm working with a LET function, my excel becomes highly unstable (it crashes without warning) when I try to go to the formula bar and try to copy the formula (so I can paste it here). Really weird. This is the function translated to a Dutch Excel:

=LET(
rng; JANUARI:DECEMBER!D6:H15;
arr; VERT.STAPELEN(rng);
n; BLADEN(rng);
c; NAAR.KOLOM(ALS(REEKS(;RIJEN(arr)/n);REEKS(n)));
r; KIES.KOLOMMEN(arr;1;2;3;5)&"";
v; KIES.KOLOMMEN(arr;4);
i; NEMEN(arr;;1);
p; DRAAIEN.PER(r;c;v;SOM;;0;;1;;i<>"SELECTEER");
a; FILTER(p;NEMEN(p;;1)<>"");
HOR.STAPELEN(a;NEMEN(a;;-1)/NEMEN(GROEPEREN.PER(r;v;RIJEN;;0;;(i<>"SELECTEER")*(i<>""));;-1))
)

 

[Bartjeuh78]

Thank you for the reply and the translation. I’m currently nor able to test in. As soon as I’m back at home tonight I’ll test ir and try it. I’ll let you guys know if it works.

 

[djclements]

Sounds good. Thanks @Rijnsent for the translation. Incidentally, I too had issues with Excel crashing when trying to highlight the entire formula (to copy and paste it here). I thought maybe it was just a one-off, but since you also experienced the same behavior, I suspect it's a glitch caused by Excel trying to evaluate the formula in the formula bar. If you copy it in smaller portions (e.g. the top half of the formula first, then the bottom half), it doesn't cause an app crash, presumably because Excel doesn't evaluate a highlighted section if it contains an incomplete formula. I'm guessing the crash in this particular case is due to a combination of the 3D range reference and one of the functions used (possibly PIVOTBY and/or GROUPBY, or maybe even the SHEETS function), but I haven't done any extensive testing as of yet to try to narrow it down.

Just a follow up note regarding the filter logic used: I specifically chose to FILTER the results of PIVOTBY afterwards (as opposed to using the [filter_array] argument to also remove blank "" cells), in an attempt to ensure the final output included all 12 months. If you only want to include the months containing data in the final output, you could use the same filter criteria for both PIVOTBY and GROUPBY, plus a few additional steps to make the header row dynamic:

=LET(
    rng, JANUARI:DECEMBER!D6:H15,
    arr, VSTACK(rng),
    n, SHEETS(rng),
    c, TOCOL(IF(SEQUENCE(,ROWS(arr)/n),SEQUENCE(n))),
    r, CHOOSECOLS(arr,1,2,3,5)&"",
    v, CHOOSECOLS(arr,4),
    i, (TAKE(arr,,1)<>"SELECTEER")*(TAKE(arr,,1)<>""),
    p, PIVOTBY(r,c,v,SUM,,0,,1,,i),
    h, DROP(TAKE(p,1),,4),
    a, DROP(p,1),
    VSTACK(
        HSTACK("Soort","Naam","Periodiciteit","Nota",TEXT(IFERROR(h*29,"Totaal Jaar"),"mmmm"),"Gemiddelde / Maand"),
        HSTACK(a,TAKE(a,,-1)/TAKE(GROUPBY(r,v,ROWS,,0,,i),,-1))
    )
)

Cheers!