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:

	Week 1	Week 2	Week 3
Project 1	Mr A; Mr B	Mr A; Mr B	Mr A; Mr B
Project 2		Mr C	Mr C
Project 3	Mr A; Mr C	Mr A; Mr C

<tbody>
</tbody>

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:

	Week 1	Week 2	Week 3
Mr A	Project 1; Project 3	Project 1; Project 3	Project 1
Mr B	Project 1	Project 1	Project 1
Mr C	Project 3	Project 2; Project 3	Project 2

<tbody>
</tbody>

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:

	A	B	C	D
1		Week 1	Week 2	Week 3
2	Project 1	Mr A; Mr B	Mr A; Mr B	Mr A; Mr B
3	Project 2		Mr C	Mr C
4	Project 3	Mr A; Mr C	Mr A; Mr C

<tbody>
</tbody>

	F	G	H	I
1		Week 1	Week 2	Week 3
2	Mr A	Project 1; Project 3	Project 1; Project 3	Project 1
3	Mr B	Project 1	Project 1	Project 1
4	Mr C	Project C	Project 2; Project 3	Project 2

<tbody>
</tbody>

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.

	A	B	C	D	E	F	G	H	I	J
1			Week 1	Week 2	Week 3		Week 1	Week 2	Week 3
2		Project 001	Mr 01; Mr 02	Mr 01; Mr 02	Mr 01; Mr 02	Mr 01	Project 001; Project 003	Project 001; Project 003	Project 001
3		Project 002		Mr 03	Mr 03	Mr 02	Project 001	Project 001	Project 001
4		Project 003	Mr 01; Mr 03	Mr 01; Mr 03		Mr 03	Project 003	Project 002; Project 003	Project 002
5		Project 004				Mr 04
6		Project 005				Mr 05
7		Project 006				Mr 06
8		Project 007				Mr 07
9		Project 008				Mr 08
10		Project 009				Mr 09
11		Project 010				Mr 10
12		Project 011				Mr 11
13		Project 012				Mr 12
14		Project 013				Mr 13
15		Project 014				Mr 14
16		Project 015				Mr 15
17		Project 016				Mr 16
18		Project 017				Mr 17
19		Project 018				Mr 18
20		Project 019				Mr 19
21		Project 020				Mr 20
22
***	**	***********	************	************	************	******	**********************	**********************	**********************	**

<tbody>
</tbody>

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.