Formula Needed for: FIND "YES" and Return in same cell as YES

[NTCICmac]

Hi MrExcel Community,

See below for a sample of data that I need to extract information into a searchable database. All of the information is provided in both columns and rows. I'd like to create TABLE 1 into TABLE 2. What's most frustrating is identifying project type by finding the YES and figuring out how to return the project type which is in the same cell!

What would be the most efficient formula to convert TABLE 1 to TABLE 2 format?

Thank you!

TABLE 1

Project #	Project Owner	Commercial:	Bank:
Project Name	Owner Address	School:	Carriage House:
Project Address	Owner City, State Zip	Office:	Industrial:
Project City, State Zip		Theatre:	Rail Station:
Project Date		Single Family Housing:	Jail:
Project Cost		Multi-family Housing (2):	Private Club:
		Multi-family Housing (3+): YES	Public:
		Hotel/B&B:	Church:
		Hospital:	Barn:
			Other:

<tbody>
</tbody>

TABLE 2

Project #	Project Name	Project Address	Project City, ST Zip	Project Date	Project Cost	Project Owner	Owner Address	Owner City, ST Zip	Project Type
Project #	Project Name	Project Address	Project City, ST Zip	Project Date	Project Cost	Project Owner	Owner Address	Owner City, ST Zip	Multi-family Housing (3+)

<tbody>
</tbody>

 

[ExcelSpeedster]

If you have the value "Multi-family Housing (3+): YES" in cell A1, the following formula will check for a "YES" and extract the Project Type only:
=IF(SEARCH("YES",A1)>-1,LEFT(A1,SEARCH(":",A1)-1))

If you want to check for a "YES" in any Project Type, you're better of using a macro to accomplish this.

 

[NTCICmac]

If you have the value "Multi-family Housing (3+): YES" in cell A1, the following formula will check for a "YES" and extract the Project Type only:
=IF(SEARCH("YES",A1)>-1,LEFT(A1,SEARCH(":",A1)-1))

If you want to check for a "YES" in any Project Type, you're better of using a macro to accomplish this.

I do want to check for a YES for any project type. Any tips on how to create a macro for it? Thanks!

 

[ExcelSpeedster]

Any tips on how to create a macro for it? Thanks!

Please send me an example file. I'll need to understand a bit more about your workflow here.

Are you updating multiple lines in Table 2 at the same time, or do you only update Table 2 with a single line once Table 1 has been completed by a user?

 

[NTCICmac]

Please send me an example file. I'll need to understand a bit more about your workflow here.

Are you updating multiple lines in Table 2 at the same time, or do you only update Table 2 with a single line once Table 1 has been completed by a user?

Thanks so much for responding! A colleague was able to figure it out for me with 3 nested formulas:

=IF(RIGHT(OFFSET($A5,R$2,R$3),3)="yes",R$1,"") for Commercial
=IF(RIGHT(OFFSET($A5,S$2,S$3),3)="yes",S$1,"") for School
=IF(RIGHT(OFFSET($A5,T$2,T$3),3)="yes",T$1,"") for Office
etc.

Problem solved! Thank you all!