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

NTCICmac

New Member
Joined
Mar 20, 2017
Messages
3
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 OwnerCommercial:Bank:
Project NameOwner AddressSchool:Carriage House:
Project AddressOwner City, State ZipOffice:Industrial:
Project City, State ZipTheatre:Rail Station:
Project DateSingle Family Housing:Jail:
Project CostMulti-family Housing (2):Private Club:
Multi-family Housing (3+): YESPublic:
Hotel/B&B:Church:
Hospital:Barn:
Other:

<tbody>
</tbody>
















TABLE 2
Project #Project NameProject AddressProject City, ST ZipProject DateProject CostProject OwnerOwner AddressOwner City, ST ZipProject Type
Project #Project NameProject AddressProject City, ST ZipProject DateProject CostProject OwnerOwner AddressOwner City, ST ZipMulti-family Housing (3+)

<tbody>
</tbody>
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

ExcelSpeedster

Board Regular
Joined
Mar 20, 2017
Messages
60
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

New Member
Joined
Mar 20, 2017
Messages
3
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

Board Regular
Joined
Mar 20, 2017
Messages
60
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

New Member
Joined
Mar 20, 2017
Messages
3
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,837
Messages
5,766,721
Members
425,373
Latest member
ndiejennrrd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top