Formula Help! Evaluation cells that contain certain text

[missdeannamarie]

Hello!! I am hoping you excel genius' can help a girl out!!! I have tried to explain below my situation. Please offer any help you think you can! Much appreciated.


TAB 1: Data is entered manually in cells B2:J3

The data in the cells is as follows:​

AA: BB: CC, CC​

Where AA is the Location of the Project, BB is the Tool, and CC are the employee's assigned to the project.​

	A	B	C	D	E	F	G	H	I	J
1	PROJECT	6/1/17	6/2/17	6/3/17	6/4/17	6/5/17	6/6/17	6/7/17	6/8/17	6/9/17
2	P1	PC: F3: KW,MG	PC: F3: KW,MG	PC: F3: KW,MG	PC: F3: KW,MG	PC: F3: KW,MG	PC: F3: KW,MG	PC: F3: KW,MG
3	P2					CS: F1: KW,DL	CS: F1: KW,DL	CS: F1: KW,DL	CS: F1: KW,DL	CS: F1: KW,DL

<tbody>
</tbody>

TAB 2: This is where I want to enter the formula. I want this to act as a resource tool, that will show when each employee (CC) is scheduled for a project. I have one formula that works to tell me if an employee is double booked, but i want to add to this.

=IF(COUNTIF(TAB1!B2:B3,"*"&$A$2&"*")=1,"Z",IF(COUNTIF(TAB1!B2:B3,"*"&$A$2&"*")>1,"X",""))
Where Z = Booked once, X = Booked more than once

I want to add to this the ability to code the booked one as its location (AA) form TAB 1. This is where I need help!
I was trying something like this, but couldn't get it to work:
=IF(AND(B57:B58="PC",B57:B58="KW"),"V", "")

Once I have the letters returned, I then conditional format based on that for color coding.

	A	B	C	D	E	F	G	H	I	J
1	EMPLOYEE	6/1/17	6/2/17	6/3/17	6/4/17	6/5/17	6/6/17	6/7/17	6/8/17	6/9/17
2	KW
3	MG
4	DL

<tbody>
</tbody>

Hope this makes sense!! Thanks a lot!!

 

[Aladin Akyurek]

What is the value that must appear in B2 of TAB2, given the sample from TAB1?

 

[missdeannamarie]

What is the value that must appear in B2 of TAB2, given the sample from TAB1?

So my goal is to have several different options:


Since the AA data in the example in TAB1 can have several different options, I want the value in B2 to have several options as well.

So for example:

In cell B2: Since "PC" is the (AA) value in TAB 1 B2, I'd like it to return "Z", if it was "CS" instead it would return "V". However, if the employee "KW" is listed more than once in the TAB 1 data range, I want it to override the other values and return "X" for example.

That's why the first formula I have works, however I don't have the option to add different values for different (AA) values.

So my vision would be this as the return:

	A	B	C	D	E	F	G	H	I	J
1	EMPLOYEE	6/1/17	6/2/17	6/3/17	6/4/17	6/5/17	6/6/17	6/7/17	6/8/17	6/9/17
2	KW	Z	Z	Z	Z	X	X	X	V	V
3	MG	Z	Z	Z	Z	Z	Z	Z
4	DL					V	V	V	V	V

<tbody>
</tbody>

Thanks!!!!

 

[Aladin Akyurek]

In B2 of TAB2 control+shift+enter, not just enter, copy across, and down:

=CHOOSE(SUM(IF(ISNUMBER(SEARCH($A2&",",SUBSTITUTE(INDEX('TAB1'!$B$2:$J$3,0,MATCH(B$1,'TAB1'!$B$1:$J$1,0))," ",",")&",")),1))+1,"",LOOKUP(9.99E+307,SEARCH({"PC:";"CS:"},LEFT(IF(ISNUMBER(SEARCH($A2&",",SUBSTITUTE(INDEX('TAB1'!$B$2:$J$3,0,MATCH(B$1,'TAB1'!$B$1:$J$1,0))," ",",")&",")),SUBSTITUTE(INDEX('TAB1'!$B$2:$J$3,0,MATCH(B$1,'TAB1'!$B$1:$J$1,0))," ",",")&","),3)),{"Z";"V"}),"X")

 

[missdeannamarie]

In B2 of TAB2 control+shift+enter, not just enter, copy across, and down:

=CHOOSE(SUM(IF(ISNUMBER(SEARCH($A2&",",SUBSTITUTE(INDEX('TAB1'!$B$2:$J$3,0,MATCH(B$1,'TAB1'!$B$1:$J$1,0))," ",",")&",")),1))+1,"",LOOKUP(9.99E+307,SEARCH({"PC:";"CS:"},LEFT(IF(ISNUMBER(SEARCH($A2&",",SUBSTITUTE(INDEX('TAB1'!$B$2:$J$3,0,MATCH(B$1,'TAB1'!$B$1:$J$1,0))," ",",")&",")),SUBSTITUTE(INDEX('TAB1'!$B$2:$J$3,0,MATCH(B$1,'TAB1'!$B$1:$J$1,0))," ",",")&","),3)),{"Z";"V"}),"X")

That worked thank you! If I want to add additional "AA" values, how would I do that? Just continue the sequence you have?

I have 8 different "AA" values I'd like to use.

 

[Aladin Akyurek]

That worked thank you!

You are welcome.

If I want to add additional "AA" values, how would I do that? Just continue the sequence you have?

I have 8 different "AA" values I'd like to use.

Yes. You could use a vertical list for each. A vertical range of 8 cells named SList, another names RList, and plug these in the appropriate places, i.e.

SList >> {"PC:","CS:"}
RList >> {"Z";"V"}

An SList item is expected to be 3 char long including a colon at the end.