Hundreds of Lockups, Formula Inefficiency, Help (Struggling to even define it to be honest)

[Isambard]

Hi,

I have a timeline of various projects in excel with the people working on them. Each person may be working on multiple projects simultaneously and I want to create a table of all my people, showing which active projects theyÂ’re assigned to on any given week. I already have a table with the projects on down the side and the weeks along them top, populated with all the people on that project which looks something like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Week 1[/TD]
[TD]Week 2[/TD]
[TD]Week 3[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD] Mr A; Mr B[/TD]
[TD]Mr A; Mr B[/TD]
[TD]Mr A; Mr B[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD][/TD]
[TD]Mr C[/TD]
[TD]Mr C[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD] Mr A; Mr C[/TD]
[TD]Mr A; Mr C[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I now want to create a table for the active projects my people have in any given week, so for the above table that would look like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Week 1[/TD]
[TD]Week 2[/TD]
[TD]Week 3[/TD]
[/TR]
[TR]
[TD]Mr A[/TD]
[TD]Project 1; Project 3[/TD]
[TD]Project 1; Project 3[/TD]
[TD]Project 1[/TD]
[/TR]
[TR]
[TD]Mr B[/TD]
[TD]Project 1[/TD]
[TD]Project 1[/TD]
[TD]Project 1[/TD]
[/TR]
[TR]
[TD]Mr C[/TD]
[TD]Project 3[/TD]
[TD]Project 2; Project 3[/TD]
[TD]Project 2[/TD]
[/TR]
</tbody>[/TABLE]


I have got a solution to this but itÂ’s really horrible and IÂ’m sure must be inefficient in CPU terms. This is the first cell in the table:

= IF(COUNTIF(S$32,"*"&$F5&"*"),$B$32&"; ","")
&IF(COUNTIF(S$33,"*"&$F5&"*"),$B$33&"; ","")
&IF(COUNTIF(S$34,"*"&$F5&"*"),$B$34&"; ","")
&IF(COUNTIF(S$35,"*"&$F5&"*"),$B$35&"; ","")
&IF(COUNTIF(S$36,"*"&$F5&"*"),$B$36&"; ","")
&IF(COUNTIF(S$37,"*"&$F5&"*"),$B$37&"; ","")
&IF(COUNTIF(S$38,"*"&$F5&"*"),$B$38&"; ","")
. . .

And on, and on, down through over 100 projects, maybe more in the future. F:F contains all the people names, and B:B the project names.

This is working, but as I say feels like a really crude solution, and I worry that in the future the amount of calculation might crash the spreadsheet. I know there are functions like sumifs, but I don’t know of anything like a “vlookup-ifs” function, and I’m not sure how I’d begin doing an array for this. Any help to get a cleaner solution than mine would be massively appreciated.

Thank you

 

[Eric W]

If you have one of the newer versions of Excel with the TEXTJOIN function, you could do this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Week 1[/TD]
[TD]Week 2[/TD]
[TD]Week 3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Project 1[/TD]
[TD]Mr A; Mr B[/TD]
[TD]Mr A; Mr B[/TD]
[TD]Mr A; Mr B[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Project 2[/TD]
[TD][/TD]
[TD]Mr C[/TD]
[TD]Mr C[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Project 3[/TD]
[TD]Mr A; Mr C[/TD]
[TD]Mr A; Mr C[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Week 1[/TD]
[TD]Week 2[/TD]
[TD]Week 3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mr A[/TD]
[TD]Project 1; Project 3[/TD]
[TD]Project 1; Project 3[/TD]
[TD]Project 1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mr B[/TD]
[TD]Project 1[/TD]
[TD]Project 1[/TD]
[TD]Project 1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Mr C[/TD]
[TD]Project C[/TD]
[TD]Project 2; Project 3[/TD]
[TD]Project 2[/TD]
[/TR]
</tbody>[/TABLE]


G2: =TEXTJOIN("; ",TRUE,IF(ISNUMBER(SEARCH($F2,B$2:B$4)),$A$2:$A$4,""))
confirmed with Control+Shift+Enter.

Copy G2 to the right and down as needed.

If you don't have TEXTJOIN, you'll need to either use a long formula like you have, or use VBA in some manner. For example, I have a UDF that mimics the way TEXTJOIN works. If you want to try that, let me know.

 

[markmzz]

Hi,

I have a timeline of various projects in excel with the people working on them. Each person may be working on multiple projects simultaneously and I want to create a table of all my people, showing which active projects they’re assigned to on any given week. I already have a table with the projects on down the side and the weeks along them top, populated with all the people on that project which looks something like:

I now want to create a table for the active projects my people have in any given week, so for the above table that would look like:

I have got a solution to this but it’s really horrible and I’m sure must be inefficient in CPU terms. This is the first cell in the table:

And on, and on, down through over 100 projects, maybe more in the future. F:F contains all the people names, and B:B the project names.

This is working, but as I say feels like a really crude solution, and I worry that in the future the amount of calculation might crash the spreadsheet. I know there are functions like sumifs, but I don’t know of anything like a “vlookup-ifs” function, and I’m not sure how I’d begin doing an array for this. Any help to get a cleaner solution than mine would be massively appreciated.

Thank you

Hi!

Maybe the Array Formula below in G2 and copy down and to the right can helps.

Use Ctrl+Shift+Enter to enter the formula

=IFERROR(INDEX($B$2:$B$101,SMALL(IF(ISNUMBER(SEARCH("*; "&$F2&";*","; "&INDEX($C$2:$E$101,,MATCH(G$1,$C$1:$E$1,0))&";")),ROW($B$2:$B$101)-ROW($B$2)+1),1)),"")&
IFERROR("; "&INDEX($B$2:$B$101,SMALL(IF(ISNUMBER(SEARCH("*; "&$F2&";*","; "&INDEX($C$2:$E$101,,MATCH(G$1,$C$1:$E$1,0))&";")),ROW($B$2:$B$101)-ROW($B$2)+1),2)),"")&
IFERROR("; "&INDEX($B$2:$B$101,SMALL(IF(ISNUMBER(SEARCH("*; "&$F2&";*","; "&INDEX($C$2:$E$101,,MATCH(G$1,$C$1:$E$1,0))&";")),ROW($B$2:$B$101)-ROW($B$2)+1),3)),"")

Ps: the formula above works for until 3 projects for people for week. You can change the formula for more projects.

[TABLE="class: grid, width: 964"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Week 1[/TD]
[TD]Week 2[/TD]
[TD]Week 3[/TD]
[TD][/TD]
[TD]Week 1[/TD]
[TD]Week 2[/TD]
[TD]Week 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Project 001[/TD]
[TD]Mr 01; Mr 02[/TD]
[TD]Mr 01; Mr 02[/TD]
[TD]Mr 01; Mr 02[/TD]
[TD]Mr 01[/TD]
[TD]Project 001; Project 003[/TD]
[TD]Project 001; Project 003[/TD]
[TD]Project 001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Project 002[/TD]
[TD][/TD]
[TD]Mr 03[/TD]
[TD]Mr 03[/TD]
[TD]Mr 02[/TD]
[TD]Project 001[/TD]
[TD]Project 001[/TD]
[TD]Project 001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Project 003[/TD]
[TD]Mr 01; Mr 03[/TD]
[TD]Mr 01; Mr 03[/TD]
[TD][/TD]
[TD]Mr 03[/TD]
[TD]Project 003[/TD]
[TD]Project 002; Project 003[/TD]
[TD]Project 002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Project 004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Project 005[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Project 006[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 06[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]Project 007[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 07[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]Project 008[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 08[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]Project 009[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 09[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]Project 010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]Project 011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]Project 012[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]Project 013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]Project 014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]Project 015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]Project 016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD]Project 017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD]Project 018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD]Project 019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD]Project 020[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mr 20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**[/TD]
[TD]***********[/TD]
[TD]************[/TD]
[TD]************[/TD]
[TD]************[/TD]
[TD]******[/TD]
[TD]**********************[/TD]
[TD]**********************[/TD]
[TD]**********************[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[Isambard]

Thanks all. I'm really pleased to know that at least there are options. I will try some of these next week when I have some time. Thanks a lot.