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

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

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" wrote in message
oups.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

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

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

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

If I put this formula in A1

=INDEX(Sheet3!$A$1:$A$50,SMALL(IF(LEN(SUBSTITUTE(U PPER(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" wrote in message
oups.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....

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

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" wrote in message
oups.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....

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

I UNDERSTAND THE LOGIC, IT JUST LOOKS A LITTLE FUZZY TO ME
IS THEIR ANY WAY YOU CAN EMAIL ME A SAMPLE WORKBOOK TO

THANKS SO MUCH FOR YOUR HELP....
MATT

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

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

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

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