Create helper column which returns a text string based on multiplecriteria.

  • Thread starter Thread starter Pierre
  • Start date Start date
P

Pierre

Have a column of data containng production codes, and need a helper
column to categorize them.
Need to identify a few characters within the cells contents, and
return another value based on what it finds:

Source data looks like this:
V009.ELE120
V144.128
V785.ELE150
V450.ELE22
T543.ELE295
Y564.ELE965
G125701.DYS021

If the cells contents contain a ELE120 or ELE150 a "Production" would
be returned. Any other ELE's (not containing the 120 or 150
immediately following) would return "Sunshine".
If the cell contains DYS, another value would be returned.
Data to be looked up is preceded by the period. All other characters
can be ignored.

Tried: =IF(A1="*ELE120*","Production",IF(A1="*ELE150*"Production",IF
(A1="*DYS*","System")))

But the wildcard of having ELE coupled with 2 or 3 additional digits
make this formula cumbersome. All the other ELE's would be something
else. I think there's something wrong with the wildcard* character.

ideas?

Thanks for any help.
Pierre
 
Try this:

=IF(OR(COUNTIF(A1,"*.ELE"&{120,150})),"Production",IF(COUNTIF(A1,"*.ELE*"),"Sunshine",IF(COUNTIF(A1,"*.DYS*"),"System","")))
 
Try this:

=IF(OR(COUNTIF(A1,"*.ELE"&{120,150})),"Production",IF(COUNTIF(A1,"*.ELE*"),­"Sunshine",IF(COUNTIF(A1,"*.DYS*"),"System","")))

--
Biff
Microsoft Excel MVP










- Show quoted text -
Click to expand...

Thank you, yet once again. I annoint you: "Sir Genius".

Pierre
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Try this:

=IF(OR(COUNTIF(A1,"*.ELE"&{120,150})),"Production",IF(COUNTIF(A1,"*.ELE*"),­"Sunshine",IF(COUNTIF(A1,"*.DYS*"),"System","")))

--
Biff
Microsoft Excel MVP










- Show quoted text -
Click to expand...

Thank you, yet once again. I annoint you: "Sir Genius".

Pierre
 
Back
Top