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

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
**********************************************
--
Tim Williams
Palo Alto, CA


"Tim Williams" <timjwilliams at gmail dot com> wrote in message
news:e8mJD70KGHA.2628@TK2MSFTNGP15.phx.gbl...
> 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
> Palo Alto, CA
>
>
> "mjj047s" <mattjohnston333@netscape.net> wrote in message
> news:1139248784.941455.159530@g47g2000cwa.googlegroups.com...
> > 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
> >
>
>

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....

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.

--
Regards,
Tom Ogilvy

"mjj047s" <mattjohnston333@netscape.net> wrote in message
news:1139253380.297203.230220@g43g2000cwa.googlegroups.com...
> 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....
>

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

Tim

--
Tim Williams
Palo Alto, CA


"mjj047s" <mattjohnston333@netscape.net> wrote in message
news:1139253380.297203.230220@g43g2000cwa.googlegroups.com...
> 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....
>

I UNDERSTAND THE LOGIC, IT JUST LOOKS A LITTLE FUZZY TO ME
IS THEIR ANY WAY YOU CAN EMAIL ME A SAMPLE WORKBOOK TO
MATTJOHNSTON333@NETSCAPE.NET
THANKS SO MUCH FOR YOUR HELP....
MATT

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

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