Complex Task Management Array formulas.

[DariusCFi]

Hello, I have been able to find parts of what I need but couldn't make them work properly together. I am by no means any expert in Excel and tbh what I have so far is from internet. I frankly don`t understand more than half of what these formulas state but some of them work just as intended. This may be one of those things that cannot be done and if so, I would appreciate much if you help me realise that.
However, better would be if anyone can help with a solution.

I hope the images will suffice in showcasing the concept I'm trying to pull off.

Desired Outcome
(everything has been edited manually to showcase)

First picture explains the concept and the second is a complete scenario.​

Where I got stuck. Please find formulas below as well.

COUNTIF($B$2:$E$5;H2)

{INDEX($H$2:$H$5;MATCH(1;SIGN(COUNTIF($K1:K$1;$H$2:$H$5)<SUMIF($H$2:$H$5;$H$2:$H$5;$I$2:$I$5));0))}

{INDEX($A$2:$A$5;MATCH(1;MMULT(--($B$2:$E$5=K2);TRANSPOSE(COLUMN($B$2:$E$5)^0));0))}

Can anyone make the last array formula return all the matching Tasks properly and integrate the percentage criteria in both arrays? Feel free to use helper columns. Ideally w/o VBA.

Thank you very much!

 

[KP117]

Hi,
Reference to the second picture.

Try this at H19 and down
CONCAT(IF(IF($F$19:$F$23<>1,$B$19:$E$23,"")="A",$A$19:$A$23,""))&CONCAT(IF(IF($F$19:$F$23<>1,$B$19:$E$23,"")="B",$A$19:$A$23,""))&CONCAT(IF(IF($F$19:$F$23<>1,$B$19:$E$23,"")="C",$A$19:$A$23,""))&CONCAT(IF(IF($F$19:$F$23<>1,$B$19:$E$23,"")="D",$A$19:$A$23,""))

at I19 and down
=MID(CONCAT(IF(IF($F$19:$F$23<>1,$B$19:$E$23,"")="A",$A$19:$A$23,""))&CONCAT(IF(IF($F$19:$F$23<>1,$B$19:$E$23,"")="B",$A$19:$A$23,""))&CONCAT(IF(IF($F$19:$F$23<>1,$B$19:$E$23,"")="C",$A$19:$A$23,""))&CONCAT(IF(IF($F$19:$F$23<>1,$B$19:$E$23,"")="D",$A$19:$A$23,"")),1+(ROW($A$1:$A$30)-1)*6,6)

Hope it helps.

 

[DariusCFi]

Hello @KP117, thank you for your time.

It doesn`t work (#Name). And even if it did, this would not solve my problem. The table on which I'll apply this concept is miles long in rows and columns. I can fairly say it will be imposible (unproductive rly) to create a scenario for each member (157 total atm), not to mention hard to work with when one leaves or joins the team.

 

[KP117]

Let me try again. Reference to third pic.

at K2
=IFERROR(INDEX($H$2:$H$5,TRUNC(AGGREGATE(15,6,IFERROR(MATCH(IF($F$2:$F$5<>1,$B$2:$E$5),$H$2:$H$5,0),"")+IF(LEN($B$2:$E$5)>0,ROW($B$2:$E$5),"")*($F$2:$F$5<>1)/100,ROW($A$1:$A$30))),),"")
at L2
=IFERROR(INDEX($A$1:$A$5,--RIGHT(AGGREGATE(15,6,IFERROR(MATCH(IF($F$2:$F$5<>1,$B$2:$E$5),$H$2:$H$5,0),"")+IF(LEN($B$2:$E$5)>0,ROW($B$2:$E$5),"")*($F$2:$F$5<>1)/100,ROW($A$1:$A$30)),LEN(AGGREGATE(15,6,IFERROR(MATCH(IF($F$2:$F$5<>1,$B$2:$E$5),$H$2:$H$5,0),"")+IF(LEN($B$2:$E$5)>0,ROW($B$2:$E$5),"")*($F$2:$F$5<>1)/100,ROW($A$1:$A$30)))-FIND(".",AGGREGATE(15,6,IFERROR(MATCH(IF($F$2:$F$5<>1,$B$2:$E$5),$H$2:$H$5,0),"")+IF(LEN($B$2:$E$5)>0,ROW($B$2:$E$5),"")*($F$2:$F$5<>1)/100,ROW($A$1:$A$30))))),"")

 

[DariusCFi]

@KP117 So, both of them return blank cells. at least visually. The first formula if inserted with cse returns A everywhere.

 

[KP117]

Hello DariusCFi,
I have tested these 2 formulas on Excel 362 and 2019, and they worked well. Please try again.
Select proper range, K2:K20 and L2:L20, copy the formulas and paste them into the ranges, then CSE.