Set a text value of a cell if a series of cells contains any specific text string

**tubasparky** · 09-17-2014, 10:43 AM

I've been able to piece together the following formula based on web searches, and I'm close to an answer.

Here is what I want to do:
An end cell has conditional formatting to color the cell red, green, or yellow when R, G, or Y respectively are in any of the preceding cells according to a heierarchy Where G trumps all, Y trumps, R, or blank if nothing. i.e. if all the previous cells in the row contain a Y then the end cell automatically populates a Y. But if any of the Cells contain a G then the last cell takes on a G value. Or if all are R except for 1 Y then the final cell populates as Y.

=IF(ISNUMBER(SEARCH("G",N8)),"G",IF(ISNUMBER(SEARCH("Y",N8)),"Y",IF(ISNUMBER(SEARCH("R",N8)),"R"," ")))

This works for a single cell N8. When I try to increase to a range to be N8:S8 The final cell in the row goes blank defaulting to my lowest level value.

How do I get this formula to work with a series of cells instead of just a single cell?

**Ron Coderre** · 09-17-2014, 10:57 AM

Can you post some sample scenarios and the results you want to see...based on those scenarios?

**tubasparky** · 09-17-2014, 11:19 AM

Here are some more examples. S9 is the result field where I want the formula to auto populate. I don't care about doing a check that they only use G, Y, or R because I expect people to only use those value options. I just want it to behave as follows:
Cell Example.JPG
N8 O8 P8 Q8 R8 S8 T8
Y Y Y Y G Y G (Because there is one G on the row)
R R R R R R R (Because they are all R)
R Y R R R R Y (Because there is one Y on the row)
G G G G G G G (Because they are all G)
R R G Y R R G (Because there is one G on the row)
- - - - - - - (Blank because all fields are blank)

The current formula I have does what I intend for the one cell I have in the formula. I can change to any letter and it behaves as I expect. I just can't figure out how to get it to behave that way using a series of cells in the row.

The hierarchy is as follows: if there are any cells in row with G then the final cell will be G. If there is no G and any cells have Y then the final cell will be Y. If there is no G or Y then the final cell will be R, unless all cells contain no G's, Y's, or R's then the final cell is blank.

**Ron Coderre** · 09-17-2014, 11:37 AM

This regular formula searches N8:S8 for an ordered list of letters (G,Y,R) and returns the highest scoring letter found

T8: =TRIM(MID(" GYR",MIN(INDEX(SEARCH(N8:S8," GYR"),0)),1))

Is that something you can work with?

**tubasparky** · 09-17-2014, 11:45 AM

I got a #VLUE! for that one. I don't understand where that formula is searching each cell for one of the 3 options. Can you explain that formula to me and maybe I'll see where it can work?

**Ron Coderre** · 09-17-2014, 11:52 AM

I mistook your dashes in your example to mean blank cells.
Try this formula

T8: =IFERROR(MID("GYR",MIN(INDEX(SEARCH(N8:S8,"GYR"),0)),1),"")

EDITED TO INCLUDE THIS BETTER ALTERNATIVE:

T8: =IFERROR(TRIM(MID(" GYR",MIN(INDEX(SEARCH(N8:S8," GYR"),0)),1)),"")

Does that help?

**tubasparky** · 09-17-2014, 12:40 PM

It works better than mine for all the rows. But in the case where some cells are not filled in yet it defaults to the G which is the highest level answer. I would like it to default to the highest in the row even if some cells are blank. I guess I didn't include that scenario.
For instance where "-" = blank cell

N8 O8 P8 Q8 R8 S8 T8
- - G Y R R G
- - Y Y - - Y
- - - - R R R

**Ron Coderre** · 09-17-2014, 01:09 PM

NOTE: I tweaked these to remove some unnecessary pieces:
I think this regular formula does what you want

T8: =IFERROR(MID("GYR",MIN(INDEX(SEARCH(N8:S8,"GYR")+ISBLANK(N8:S8)*10,0)),1),"")

or...if you're a fan of ARRAY FORMULAS, which are completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER), this slightly shorter formula.

T8: =IFERROR(MID("GYR",MIN(IF(N8:S8<>"",SEARCH(N8:S8,"GYR"))),1),"")

(I just avoid them because nobody I work with ever remembers to C+S+E after editing them)

Is that something you can work with?

**tubasparky** · 09-17-2014, 01:52 PM

I only get blank in the last cell no matter what I put in the others. Will you help me to understand what that formula is supposed to be doing?

BTW Ron, thank you for your help. I appreciate you working through this with me.

**Ron Coderre** · 09-17-2014, 03:39 PM

Can you attach a sample workbook so I can see what you're working with?
(I work at a major bank and can't send files out)

**tubasparky** · 09-18-2014, 07:32 AM

Ron,

Here is a sample of one row with the formatting. https://db.tt/qqPhSNa6
I have my original formula in cell T8. So if you change the status of Cell N8 it will change the status of T8 and format accordingly. That is the way I would like it to function for all 6 cells simultaneously, with the hierarchy in place of G then Y then R, and blank if all cells are blank. There may be some cells which are N/A which should be ignored.

Thanks for your help. I look forward to seeing what you come up with.

**Ron Coderre** · 09-18-2014, 09:05 AM

Let's try this regular formula in your posted workbook

T8: =IFERROR(MID("GYR",MIN(INDEX(SEARCH(LEFT(N8:S8&"Z",1),"GYRZN"),0)),1),"")

Does that help?

**tubasparky** · 09-18-2014, 09:45 AM

Awesome!! That works great. Exactly what I was looking for! Thanks so much for your help Ron.

Set a text value of a cell if a series of cells contains any specific text string

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Set a text value of a cell if a series of cells contains any specific text string

Re: Set a text value of a cell if a series of cells contains any specific text string

Re: Set a text value of a cell if a series of cells contains any specific text string

Re: Set a text value of a cell if a series of cells contains any specific text string

Re: Set a text value of a cell if a series of cells contains any specific text string

Re: Set a text value of a cell if a series of cells contains any specific text string

Re: Set a text value of a cell if a series of cells contains any specific text string

Re: Set a text value of a cell if a series of cells contains any specific text string

Re: Set a text value of a cell if a series of cells contains any specific text string

Re: Set a text value of a cell if a series of cells contains any specific text string

Re: Set a text value of a cell if a series of cells contains any specific text string

Re: Set a text value of a cell if a series of cells contains any specific text string

Re: Set a text value of a cell if a series of cells contains any specific text string

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Redistributing text values in a text string in one cell into multiple cells as numbers

[SOLVED] To search specific text in a cell that contains text string

Search a string for a specific text value from cells

[SOLVED] Find partial text string within another text string return original text into cell.

[SOLVED] Macro to convert specific text and format specific text within a string

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions