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

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

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

--
Regards,
Tom Ogilvy

"mjj047s" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
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!!!!!