Create a new text field based on two text fields and 3 or more variables

[Mary755]

My platform is Windows 7 and I use Excel 2010.

I receive a worksheet everymonth that contains 2 fields, State_Of_Area and Area_Name. I need to create a new column of the Area_Name based the those two fields, and a 3rd field I place in the worksheet named Acceptable States.

If the Area_Name = "Outside Area - Name of State" and the State_Of_Area is listed in the field of Acceptable States, the Area_Name becomes "InstateOutOfArea." However, if the state in State_Of_Area is CA, which is an acceptable state, the Area_Name becomes "CA_InStateOutOfArea." States not listed in the list of Acceptable States are all named "Outside." Everything else keeps the same name in the new Area_Name field.

I am using a 3 step process to arrive at this, replacing the name as is unles it contains the word "Outside". The next step is to replace the name if the State_Of_Area is in an acceptable state, and then finding California and fixing those names. There must be a way to do this with one formula.

I've found plenty of examples based on numbers fields, but not so many for text fields.

Thanks in advance for your help.

ACCEPTABLE STATES STATE_OF_AREA AREA NAME FINAL RESULTS FOR AREA NAME SHOULD BE AR AZ EXAMPLENAME_AZ EXAMPLENAME_AZ AZ CA YOSEMITE_CA YOSEMITE_CA CA CA ANOTHERAREA_CA ANOTHERAREA_CA CO CA BYOCEAN_CA BYOCEAN_CA FL CA BYOCEAN_CA BYOCEAN_CA IA CA Outside Areas - California CA_InStateOutOfArea ID CO MOUNTAINS_CO MOUNTAINS_CO IL CO Outside Areas - Coloarda InStateOutOfArea IN FL ATLANTIC_FL ATLANTIC_FL KS FL DISNEYAREA_FL DISNEYAREA_FL KY HI Outside Areas - Hawaii Outside MN IL CHICAGO_IL CHICAGO_IL MO KS AREA3_KS AREA3_KS MT KS AREA3_KS AREA3_KS ND KS Outside Areas - Kansas InStateOutOfArea NE KY GREEN_KY GREEN_KY NM LA Outside Areas - Louisiana Outside NV MI Outside Areas - Michigan Outside OH MN NORTHERN_MN NORTHERN_MN OR MN SOUTHERN_MN SOUTHERN_MN SD WY Outside Areas - Wyoming InStateOutOfArea TN CA Outside Areas - California CA_InStateOutOfArea UT TN MUSICLAND_TN MUSICLAND_TN WA WI Wisconsin NonCity Areas Wisconsin NonCity Areas WI PA Pennsylvanina NonCity Areas Outside WY WI Wisconsin NonCity Areas Wisconsin NonCity Areas WI Wisconsin NonCity Areas Wisconsin NonCity Areas KS Outside Areas - Kansas InStateOutOfArea <TBODY> </TBODY>

<TBODY>
</TBODY>

 

[ChrisBM]

Assuming your cells are in columns A:D and your header rows is 1:

Try the following formula in cell D2:

=IF(ISNUMBER(MATCH(B2,$A:$A,0)),IF(LEFT(C2,7)="Outside", B2 & "_InStateOutofArea"), "Outside")

 

[Rick Rothstein]

Give this macro a try...

Sub AreaNames()
  Dim R As Long, StateAndArea As Variant, Final As Variant, Acceptable As Variant
  Acceptable = Application.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp)))
  StateAndArea = Range("B2:C" & Cells(Rows.Count, "B").End(xlUp).Row)
  ReDim Final(1 To UBound(StateAndArea), 1 To 1)
  For R = 1 To UBound(StateAndArea)
    If StateAndArea(R, 2) Like "Outside Area*" Then
      If StateAndArea(R, 1) = "CA" Then
        Final(R, 1) = "CA_InStateOutOfArea"
      ElseIf UBound(Filter(Acceptable, StateAndArea(R, 1))) = 0 Then
        Final(R, 1) = "InStateOutOfArea"
      Else
        Final(R, 1) = "Outside"
      End If
    ElseIf UBound(Filter(Acceptable, StateAndArea(R, 1))) = 0 Then
      Final(R, 1) = StateAndArea(R, 2)
    Else
      Final(R, 1) = "Outside"
    End If
  Next
  Range("D2").Resize(UBound(StateAndArea)) = Final
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (AreaNames) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Rick Rothstein]

Give this macro a try...

And here is a formula solution that appears to do the same thing as the macro I posted. Place the following formula in D2 and copy down...

=IF(LEFT(C2,12)="Outside Area",IF(B2="CA","CA_InStateOutOfArea",IF(ISNUMBER(MATCH(B2,A$2:A$27,0)),"InStateOutOfArea","Outside")),IF(ISNUMBER(MATCH(B2,A$2:A$27,0)),C2,"Outside"))

 

[Mary755]

You guys are geniuses!

Rick Rothstein, I used your formula and it worked perfectly! Thank you Thank you!!! I do use macros on some spreadsheets, but really do like inputting the formulas as it helps me remember how things work.

ChrisMB, your's returned a true/false statement, so I think it was missing one element.

I'm not sure how to mark this as solved, but I got my answer and it worked.

Again, thank you so much for the solutions.

Mary