Dynamic Array needed

[Stephen_IV]

I have VBA for this but I am looking to solve this with your help with a Dynamic Array. I am looking for a Dynamic array of employees and schools. Each x represents 0.2 for a day. Sometimes a person will be at multiple schools in a day. So if the employee goes to 2 schools in a day then it would be 0.1 for the first school and 0.1 for the second school.

For employee 111

ID	Last Name	First Name	School	M	T	W	TH	F
111​	AAA	A	Washington			x	x
111​	AAA	A	Lincoln	x	x	x	x	x

In this example 111 at School Washington Works W and TH. Wednesday is also at Lincoln and Thursday at Lincoln so 0.1 for W and 0.1 for TH = 0.2.
At Lincoln M 0.2, T 0.2, W 0.1, TH 0.1, F 0.2 = 0.8

Book1 A B C D E F G H I J 1 ID Last Name First Name School M T W TH F 2 111 AAA A Washington x x 0.2 3 111 AAA A Lincoln x x x x x 0.8 4 222 BBB B Ford x 0.1 5 222 BBB B Washington x x x x x 0.9 6 333 CCC C Harding x x x x x 1 7 444 DDD D Central x x x 0.233333333 8 444 DDD D Lincoln x x x x x 0.633333333 9 444 DDD D Ford x x 0.133333333 10 555 EEE E Johnson x x x 0.4 11 555 EEE E Kennedy x x x x 0.4 12 555 EEE E Roosevelt x x 0.2 Sheet1

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ID	Last Name	First Name	School	M	T	W	TH	F
2	111	AAA	A	Washington			x	x		0.2
3	111	AAA	A	Lincoln	x	x	x	x	x	0.8
4	222	BBB	B	Ford					x	0.1
5	222	BBB	B	Washington	x	x	x	x	x	0.9
6	333	CCC	C	Harding	x	x	x	x	x	1
7	444	DDD	D	Central	x	x	x			0.23333333
8	444	DDD	D	Lincoln	x	x	x	x	x	0.63333333
9	444	DDD	D	Ford	x	x				0.13333333
10	555	EEE	E	Johnson			x	x	x	0.4
11	555	EEE	E	Kennedy	x	x	x	x		0.4
12	555	EEE	E	Roosevelt	x	x				0.2
13
Data

Cell Formulas
Range	Formula
J2:J12	J2	=BYROW(A2:I12,LAMBDA(br,SUM(0.2/BYCOL(FILTER($E$2:$I$100,$A$2:$A$100=TAKE(br,,1)),LAMBDA(bc,SUM(--(bc="x"))))*(TAKE(br,,-5)="x"))))
Dynamic array formulas.

 

[Stephen_IV]

Fluff
It is 95% there! It gives me an error when not all 5 are filled in like below.

1008	zzz	z	Thomas Hooker	x		x
1008	zzz	z	Washington	x		x		x

 

[Fluff]

How about

=BYROW(A2:I12,LAMBDA(br,SUM(IFERROR(0.2/BYCOL(FILTER($E$2:$I$100,$A$2:$A$100=TAKE(br,,1)),LAMBDA(bc,SUM(--(bc="x"))))*(TAKE(br,,-5)="x"),0))))

 

[Stephen_IV]

How about

=BYROW(A2:I12,LAMBDA(br,SUM(IFERROR(0.2/BYCOL(FILTER($E$2:$I$100,$A$2:$A$100=TAKE(br,,1)),LAMBDA(bc,SUM(--(bc="x"))))*(TAKE(br,,-5)="x"),0))))

Beautiful!!! Thank you very much. Worked great. I appreciate all of the help!!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[felixstraube]

Another option:
The more readable (col J - LET function)
Or the condensed (col K)

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Last Name	First Name	School	M	T	W	TH	F
2	111	AAA	A	Washington			x	x		0.2	0.2
3	111	AAA	A	Lincoln	x	x	x	x	x	0.8	0.8
4	222	BBB	B	Ford					x	0.1	0.1
5	222	BBB	B	Washington	x	x	x	x	x	0.9	0.9
6	333	CCC	C	Harding	x	x	x	x	x	1	1
7	444	DDD	D	Central	x	x	x			0.233333	0.233333
8	444	DDD	D	Lincoln	x	x	x	x	x	0.633333	0.633333
9	444	DDD	D	Ford	x	x				0.133333	0.133333
10	555	EEE	E	Johnson			x	x	x	0.4	0.4
11	555	EEE	E	Kennedy	x	x	x	x		0.4	0.4
12	555	EEE	E	Roosevelt	x	x				0.2	0.2
Sheet4

Cell Formulas
Range	Formula
J2:J12	J2	=LET(cId, A2, id, $A$2:$A$12, d, ($E$2:$I$12<>"")*1, r, (E2:I2<>"")*1, fd, FILTER(d,id=cId), SUM(0.2/BYCOL(fd,LAMBDA(x,SUM(x)))*r) )
K2:K12	K2	=SUM(0.2/BYCOL(FILTER(($E$2:$I$12<>"")*1,$A$2:$A$12=A2),LAMBDA(x,SUM(x)))*(E2:I2<>""))

 

[Fluff]

Not sure if you saw post#3 as your formula will have the same problem as mine did.

 

[felixstraube]

You are right. My mistake.

here is the correction:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Last Name	First Name	School	M	T	W	TH	F
2	111	AAA	A	Washington			x	x		0.3	0.3
3	111	AAA	A	Lincoln	x		x		x	0.5	0.5
4	222	BBB	B	Ford					x	0.1	0.1
5	222	BBB	B	Washington	x	x	x	x	x	0.9	0.9
6	333	CCC	C	Harding	x	x	x	x	x	1	1
7	444	DDD	D	Central	x	x	x			0.233333	0.233333
8	444	DDD	D	Lincoln	x	x	x	x	x	0.633333	0.633333
9	444	DDD	D	Ford	x	x				0.133333	0.133333
10	555	EEE	E	Johnson			x	x	x	0.4	0.4
11	555	EEE	E	Kennedy	x	x	x	x		0.4	0.4
12	555	EEE	E	Roosevelt	x	x				0.2	0.2
Sheet4

Cell Formulas
Range	Formula
J2:J12	J2	=LET(cId, A2, id, $A$2:$A$12, d, ($E$2:$I$12<>"")*1, r, (E2:I2<>"")*1, fd, FILTER(d,id=cId), SUM(IFERROR(0.2/BYCOL(fd,LAMBDA(x,SUM(x)))*r,0)) )
K2:K12	K2	=SUM(IFERROR(0.2/BYCOL(FILTER(($E$2:$I$12<>"")*1,$A$2:$A$12=A2),LAMBDA(x,SUM(x)))*(E2:I2<>""),0))

 

[Stephen_IV]

Thanks felixstraube! Much appreciated!!

​