Fill cells with text if cells in column have specific text string

basicallychels

New Member
Joined
Sep 8, 2014
Messages
12
I am trying to create something that will allow me to autofill the agency column with an Agency's name if the adjacent Facility Name cell contains a certain text string. So for example, if the Facility Name is Sacramento, Union City, Ventura, Fresno, Riverside, or Cypress I want to fill the adjacent Agency cell with VIC the PICC. But if the Facility Name is Glasgow, Louisville, Westlake, etc. I want to fill the adjacent Agency cell with Access RN. I have to do this each month, so if I could find something that would be easy to apply each month, like a macro, that would be awesome. I am just not sure what the best way to go about this is, since I have multiple agencies that go with multiple facilities.



AgencyFacility Name
VIC the PICCSACRAMENTO
HOUSTON

<tbody>
</tbody>
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
If Your data starts in B2, then in A2 use this formula:
=IF(OR(B2="Sacramento", B2="Union City",B2="Ventura",B2="Fresno",B2="Riverside",B2="Cypress"),"VIC the PICC","Access RN")

It will put "Access RN" in the cell if it contains anything but Your first requirements. Not sure if you want that?
 
Upvote 0
Hello there,

Why don't you try to create a table like this

Agency Facility
VIC the PICC Sacramento
VIC the PICC Union City
VIC the PICC Ventura
Access RN Glasgow
Access RN Louisville


When you write Sacramento at Facility column in the agency column you will have this formula

=index(A1:A5, match(E1, B1:B5, 0))

E1 -> Sacramento (for instance)
match will look at range B1:b5 to find where is Sacramento and will return the row number where it was found
index shows the content of column A at row number returned by match function

Hope this help.

Vândalo
 
Upvote 0
Are there only two options?

Could you try:

=if(OR(A1="Sacramento",A1="Union City",A1="Ventura",A1="Fresno",A1="Riverside",A1="Cypress"),"VIC the PICC","Access RN")


If there are more options you can do several ifs.
 
Upvote 0
I have more than two agencies, I have 44 different agencies so unfortunately that won't work. I'm wanting to fill the Agency Column based on what's in the Facility Name Column. Each Facility (there's around 70) goes with an agency. In my workbook, I have 1032 entries for facility names, so there are many repeating facilities. Currently, I have to look up each facility by hand to see what agency covers it. I am trying to find something that will popular the agency automatically based off the contents of the facility cell. Does that make sense?
 
Upvote 0
Hi basicallychels,

Did you already read my post?
With my solution you can have as many agencies as you want.

Vândalo
 
Upvote 0
I'm confused how to set yours up. So if have the following agencies and matching facility criteria:
AccessRN: bridgeton, fridley, lenexa, omaha, macon
PICC Pros: Austin, El Paso, Houston, Lubbock, Tyler, San Antonio, Grand Prarie
VIC the PICC: Sacramento, Union City, Ventura, Fresno, Riverside, or Cypress
CIAs: Portland, Berlin, Concord Warwick, Greensurg
Quick PICC: Brockton, Memphis, Nashville, Elmwood
IV Queen: Daphne, Jackson
ETC, ETC ETC
How would I set that up? My worksheet has the facilities filled, but the agency column is blank?
 
Upvote 0
HTML:
EFGHI12AgencyFacilityAgencyFacility13Access RNBridgetonPICC ProsEl Paso14Access RNFridley15Access RNLnexa16Access RNOmaha17Access RNMacon18PICC ProsAustin19PICC ProsEl Paso20PICC ProsHouston21PICC ProsLubbock22PICC ProsTyler23PICC ProsSan Antonio24PICC ProsGrand Prarie25VIC the PICCSacramento26VIC the PICCUnion City27VIC the PICCVentura28VIC the PICCFresno29VIC the PICCRiverside30VIC the PICCCypress[CENTER][COLOR=#161120][B]Sheet1[/B][/COLOR][/CENTER]

Worksheet FormulasCellFormulaH13=INDEX($E$13:$E$30,MATCH(I13,$F$13:$F$30,0))

Hope this help.

P.S. - Hey this looks good
 
Upvote 0
AgencyFacilityAgencyFacility
Access RNBridgetonPICC ProsEl Paso
Access RNFridley
Access RNLnexa
Access RNOmaha
Access RNMacon
PICC ProsAustin
PICC ProsEl Paso
PICC ProsHouston
PICC ProsLubbock
PICC ProsTyler
PICC ProsSan Antonio
PICC ProsGrand Prarie
VIC the PICCSacramento
VIC the PICCUnion City
VIC the PICCVentura
VIC the PICCFresno
VIC the PICCRiverside
VIC the PICCCypress

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

The formula under agency is

=INDEX($E$13:$E$30,MATCH(I13,$F$13:$F$30,0))

Sorry for the inconvenience


Vândalo
 
Last edited:
Upvote 0
So what do I need to do to add the other agencies into this code? I'm not super advanced at figuring excel out by myself, but I can probably figure this out if you let me know the values that I would need to add or change. I really appreciate you guys taking the time to do this, it's going to save me a lot of work each month!
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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
Back
Top