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
[TABLE="class: outer_border, width: 700, align: left"]
<tbody>[TR]
[TD]Project #[/TD]
[TD]Project Owner[/TD]
[TD]Commercial:[/TD]
[TD]Bank:[/TD]
[/TR]
[TR]
[TD]Project Name[/TD]
[TD]Owner Address[/TD]
[TD]School:[/TD]
[TD]Carriage House:[/TD]
[/TR]
[TR]
[TD]Project Address[/TD]
[TD]Owner City, State Zip[/TD]
[TD]Office:[/TD]
[TD]Industrial:[/TD]
[/TR]
[TR]
[TD]Project City, State Zip[/TD]
[TD][/TD]
[TD]Theatre:[/TD]
[TD]Rail Station:[/TD]
[/TR]
[TR]
[TD]Project Date[/TD]
[TD][/TD]
[TD]Single Family Housing:[/TD]
[TD]Jail:[/TD]
[/TR]
[TR]
[TD]Project Cost[/TD]
[TD][/TD]
[TD]Multi-family Housing (2):[/TD]
[TD]Private Club:[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Multi-family Housing (3+): YES[/TD]
[TD]Public:[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Hotel/B&B:[/TD]
[TD]Church:[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Hospital:[/TD]
[TD]Barn:[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Other:[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
















TABLE 2
[TABLE="class: outer_border, width: 1000, align: left"]
<tbody>[TR]
[TD]Project #[/TD]
[TD]Project Name[/TD]
[TD]Project Address[/TD]
[TD]Project City, ST Zip[/TD]
[TD]Project Date[/TD]
[TD]Project Cost[/TD]
[TD]Project Owner[/TD]
[TD]Owner Address[/TD]
[TD]Owner City, ST Zip[/TD]
[TD]Project Type[/TD]
[/TR]
[TR]
[TD]Project #[/TD]
[TD]Project Name[/TD]
[TD]Project Address[/TD]
[TD]Project City, ST Zip[/TD]
[TD]Project Date[/TD]
[TD]Project Cost[/TD]
[TD]Project Owner[/TD]
[TD]Owner Address[/TD]
[TD]Owner City, ST Zip[/TD]
[TD]Multi-family Housing (3+)[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!