Search for "text" in description - if certain text - display "text" in another row

[mjj047s]

Ok - I hope I world this correctly.
I have two coumns. Column A is blank, and Column B has different item
descriptions. Instead of manually looking through column B to
determine what to type in A, can i have a formula do that?
EXAMPLE - lets use State Abbreviations -

Column B1 says - ILLINOIS DEPT OF;PAYMENT INITIATED - REVENUE ID =
3423455
another column (B2) says - DEPT OF REVENUE ; MO DOR - PAYMENT ACCEPTED
-
and so on and so on....


The purpose of Column A is to identify column B in two state letters

Every Illinois description will say " ILLINOIS DEPT OF" in column B. So
every column that says that, i want column A to say "IL"

Every Missouri description will say "MO DOR"in column B. So every
column that says that, i want column A to say "MO"

Make sense? I hope so -
basically - search for a string of text, if you find THAT STRING put
"this" in Column A, otherwise search for another string of text, if you
find THAT STRING put "that" in Column A, and so on....

sorry for being so confusing. Basically, i don't want to have to go
through 1000 descriptions every month to identify a State.


mj

 

[Tom Ogilvy]

Look at the Find and Search worksheet functions. However, this probably
isn't practical if you are looking for more than two or three states.

You might want to do it with a macro

 

[Tim Williams]

How many possible distinct strings are there ? Do you have a list of them ?

You could create a function which would scan the list and return the State.

Eg, (untested) with a sheet "List" which lists your strings in column A and
their matching states in col B

**********************************************
Function GetState(val) as string

dim retval
dim c as range
dim temp

retval="Not found"
if len(val)>0 then
'adjust the range to suit....
for each c in thisworkbook.sheets("List").range("A1:A100")

if ucase(val) like "*" & ucase(c.value) & "*" then
retval=c.offset(0,1).value
end if

next c
end if

GetState = retval
end function
*********************************************

 

[Tim Williams]

A bit improved (and tested....)

Tim

**********************************************
Option Explicit

Function GetState(val) As String

Dim retval
Dim c As Range
Dim temp

retval = "Not found"
If Len(val) > 0 Then
'adjust the range to suit....
For Each c In ThisWorkbook.Sheets("List").Range("A1:A100")

If UCase(val) Like "*" & UCase(c.Value) & "*" Then
retval = c.Offset(0, 1).Value
Exit For
End If

Next c
End If

GetState = retval
End Function
**********************************************

 

[mjj047s]

OH "DES=OH "
AL AL DEPT OF REV
AR AR SALES TAX PAY
AZ "AZ DEPT OF REV "
CA "BOARD OF EQUALIZ"
AZ "CITY CHANDLERGEN;DES"
KY "CMMNWLTH OF KY"
MA COMM OF MASS EFT
PA "COMMWLTHOFPA "
MD "COMP OF MARYLAND"
CT "CT DOR PAYMENT "
SD DEPT OF REVENUE ;OUTBACK MIDWEST II LP
CO Dept. of Revenue;ID=COLTAX
FL FLA DEPT REVENUE;
GA GEORGIA SALES
AL HUNTSVILLE
IA IA DEPT OF REV
IL IDOR
IL ILLINOIS DEPT OF
IN IN SALES/USE TAX
KS KSDEPTOFREVENUE
ME ME BUREAU OF TAX
MN "MN DEPT REVENUE "
MO MODR TAX
AL "MONTGOMERY "
NC NC DEPT OF REVEN
ND "NDTAX "
NJ NEW JERSEY EFT
NM "NEW MEXICO "
NY "NEW YORK STATE "
NH NH DEPT REVENUE
NJ NJ S&U WEB PMT
OH SALES & USE TAX ;DES=OHIOTAXES
SC "SC DEPT REVENUE "
NE "ST TREASURY/SALE; HEARTLAND I LT"
TX "STATE COMPTRLR ;DES=TEXNET"
AR STATE OF ARKANSA
LA STATE OF LOUISIA
MI STATE OF MICH
RI "STATE OF RI "
UT State Tax DES=UtahTaxEFT;
OK TAX PAYMENTS ;DES=OK TAX PMT
TN "TENN DEPT OF REV"
VA VA DEPT TAXATION
WI "WI DEPT REVENUE"
WV WVTREASURY
WY "WYDOR "

THIS IS THE LIST - THESE ARE THE IDENTIFIERS IN THE DESCRIPTIONS -
THERE IS MORE IN THE DESCRIPTIONS THOUGH....

 

[Tom Ogilvy]

If I put this formula in A1

=INDEX(Sheet3!$A$1:$A$50,SMALL(IF(LEN(SUBSTITUTE(UPPER(B1),UPPER(Sheet3!$B$1
:$B$50),""))<>LEN(B1),ROW($B$1:$B$50)),1),1)

and enter with Ctrl+shift+enter rather than just enter since it is an array
formula

then drag fill it down the column. Adjust Sheet3!$B$1:$B$50 to reflect the
location of the table below.

This worked for your Illinois example, but not for your MO example because
your table didn't contain MO DOR - it containd MODR TAX. When I put MO DOR
in the table, it worked for MO as well.

 

[Tim Williams]

Post again if you have problems using the code I suggested. It should be
placed in a standard module.

Tim

 

[mjj047s]

I UNDERSTAND THE LOGIC, IT JUST LOOKS A LITTLE FUZZY TO ME
IS THEIR ANY WAY YOU CAN EMAIL ME A SAMPLE WORKBOOK TO
(e-mail address removed)
THANKS SO MUCH FOR YOUR HELP....
MATT

 

[mjj047s]

M A T T J O H N S T O N 3 3 3 @ N E T S C A P E . N E T

 

[mjj047s]

You guys are awesome! Thank you so much for your help with this!!!!!