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.​

[TABLE="class: grid"]
<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]PROJECT[/TD]
[TD]6/1/17[/TD]
[TD]6/2/17[/TD]
[TD]6/3/17[/TD]
[TD]6/4/17[/TD]
[TD]6/5/17[/TD]
[TD]6/6/17[/TD]
[TD]6/7/17[/TD]
[TD]6/8/17[/TD]
[TD]6/9/17[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]P1
[/TD]
[TD]PC: F3: KW,MG[/TD]
[TD]PC: F3: KW,MG[/TD]
[TD]PC: F3: KW,MG[/TD]
[TD]PC: F3: KW,MG[/TD]
[TD]PC: F3: KW,MG[/TD]
[TD]PC: F3: KW,MG[/TD]
[TD]PC: F3: KW,MG[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]P2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CS: F1: KW,DL[/TD]
[TD]CS: F1: KW,DL[/TD]
[TD]CS: F1: KW,DL[/TD]
[TD]CS: F1: KW,DL[/TD]
[TD]CS: F1: KW,DL[/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="class: grid"]
<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]EMPLOYEE[/TD]
[TD]6/1/17[/TD]
[TD]6/2/17[/TD]
[TD]6/3/17[/TD]
[TD]6/4/17[/TD]
[TD]6/5/17[/TD]
[TD]6/6/17[/TD]
[TD]6/7/17[/TD]
[TD]6/8/17[/TD]
[TD]6/9/17[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]KW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]MG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]DL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid"]
<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]EMPLOYEE[/TD]
[TD]6/1/17[/TD]
[TD]6/2/17[/TD]
[TD]6/3/17[/TD]
[TD]6/4/17[/TD]
[TD]6/5/17[/TD]
[TD]6/6/17[/TD]
[TD]6/7/17[/TD]
[TD]6/8/17[/TD]
[TD]6/9/17[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]KW[/TD]
[TD]Z[/TD]
[TD]Z[/TD]
[TD]Z[/TD]
[TD]Z[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]V[/TD]
[TD]V[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]MG[/TD]
[TD]Z[/TD]
[TD]Z[/TD]
[TD]Z[/TD]
[TD]Z[/TD]
[TD]Z[/TD]
[TD]Z[/TD]
[TD]Z[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]DL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]V[/TD]
[TD]V[/TD]
[TD]V[/TD]
[TD]V[/TD]
[TD]V[/TD]
[/TR]
</tbody>[/TABLE]

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.