Excel 2007 - Populate column based on wildcard search of another colum 2

[BxWill]

Have a 3000+ row MS Excel 2007 worksheet with inconsistent data in the "Description" column (col A).

For example, I have the following in col A;

Fever
Inconsistent Fever
Pain and Fever
AbdPain
Abdominal Pain
Abdom Pain
BrokenFoot


Is it possible to perform a wildcard search of column A and populate column B with "Fever" or "Abdomen Pain" or "NotFeverOrAbdomenPain?", accordingly?

Currently experimenting with the following IF statement;

=IF(SEARCH("*Fever*",A2),"Fever",IF(SEARCH("*Abd Pain*",A2),"Abdomen Pain","NotFeverOrAbdomenPain"))

Appreciate any insight.

 

[AnotherHiggins]

FYI: You don't need to use a wildcard with the Search function. Just Search("fever"... will work.

If there's a known list of possible inputs you could build a table and use a VLookup to return the desired values.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Gavona]

I would actually just use autofilter (automating with macro if a repeated job). Formulae will get way to complex. If you create a list of things to search for and the value to put in column B then we can help you automate the routine for next time - but post in forum707 for that.
Step1: Filter B for blanks - so you are only working on unresolved values in column A

Step 2: Filter A for = *fever* (ie contains Fever)

Step 3: Select all cells in column B (see below)
Type Fever then hold Ctrl as you press enter.

Go back to Step 2: Filter A for *Abd*
......

Finally a pivot table with column fields of B and A will allow you to review what you have done.


"Select all cells in column B"
I mean just those within your table.
Possibly the easiest way to "Select all cells in column B"
Is to create a named range based on the column heading.
As a 2003 user not sure I can tell you exactly how to do that in 2007.
So an alternative: filter column B for "ghgh" or anything that does not exist. The first visible row should be the row immediately below your data table (if it isn't we need to sort that first). In column B put some text in this cell (right at the end of the process clear this cell). Then follow the steps I suggested and when you get to "Select all cells in column B" do the following:
1. click on the column B field heading (the cell with the filter arrow)
2. Down arrow will take you to the first visible cell in the column but excluding the column headings
3. Ctrl-Shft-Down will extend the selection to include all the visible (blank 'cos we have filtered for that) cells plus the cell immediately below your filter range that we just put some text in. Ctrl-Enter the value you choose into these cells



Gavin

 

[BxWill]

Thanks for the insight.

Currently, I am reviewing for 5 conditions in column A and would like to populate Columns B through D - on a daily basis;

SearchForInColA---Column B ----Column C--------Column D
Abd Pain----------AbdomenPain--LowPriority-----Don't Call
Fever-------------Fever--------NormalPriority--Call
Cough-------------Cough--------LowPriority-----Don't Call
Headach-----------Headache-----NormalPriority--Don't Call
Sickle------------SickleCell---HighPriority----Call

Interested in using VBA in Excel 2007 to automate the process but contemplating on if this can be performed using a MS Access 2007 database.

Thanks in advance for any additional insight.

The list of conditions is expected to increase to approximately 20 within the next month.

Will post to VBA forum as well.

 

[SkipVought]

You might also consider using MS Query (much like MS Access queries)

You can use the LIKE operator in the WHERE clause.

faq68-5829.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}