Microsoft Outlook 2013 32/64-bit (mail m...

DerbyDad said I'd be back so here we go! This is part of a large report and goes along with the last two questions I asked. This item I am looking at a string of cells and (hopefully!) one of the cells will have a code that is part of a list. Instead of checking each cell, I'd like to have one column that shows the 'winner', the first correct code in the list.

I know that probably sounds confusing, more so than it actually is, I am terrible at explanations so let me show you:

A B C D E F TE207 TE220B TE220B TE220B TE220B TE220B TE207 TE220B TE220B TE220B TE220B TE220B TE207 TE207 TE207 TE220B TE220B TE220B TE206 TE206 TE207 TE207 TE207 TE207 TE220A TE220B TE220B TE220B TE220B TE220B TE206 TE206 TE206 TE206 TE206 TE220A TE206 TE207 TE207 TE207 TE220B TE220BIn column G, I'd like to just show the first cell that shows one of these codes: TE207 or TE220A. There are really 8 codes I need to look for and in ten cells, the example is just shortened. I've tried an IF with an ISTEXT and a FIND and a SEARCH but can't figure out how to write a formula that will search all cells on each row and then show whichever code is first.

In row 1-3, the right cell is in column A. In row 4, it's in column C; in row 5, it's in column A; in 5 it's A; in 6 it's F, etc.

I just want one code, even if there are three correct cells in the row, just the first one will do.

Okay, ideas, please!

message edited by GingerLeake

✔ Best Answer

Here is an improved version where you do not have to combine your data. With your data like:

A B C D E F G H I 1) TE207 TE220B TE220B TE220B TE220B TE220B TE207 TE207 2) TE207 TE220B TE220B TE220B TE220B TE220B TE207 TE220A 3) TE207 TE207 TE207 TE220B TE220B TE220B TE207 4) TE206 TE206 TE207 TE207 TE207 TE207 TE207 5) TE220A TE220B TE220B TE220B TE220B TE220B TE220A 6) TE206 TE206 TE206 TE206 TE206 TE220A TE220A 7) TE206 TE207 TE207 TE207 TE220B TE220B TE207In column I enter the codes you wish to search for.

Next give your list a Defined Name, I have use the nametlistThen in cell G1 enter the

ARRAYformula:=IFERROR(INDEX(tlist,MAX(IF(ISERROR(SEARCH(tlist,CONCATENATE(A1," ",B1," ",C1," ",D1," ",E1," ",F1))),-1,1)*(ROW(tlist)-ROW($I$1)+1))),"No Match")

Don't forget to use CTRL-SHIFT-ENTER for the array.

Drag down as many rows as needed and you should get

the same as above.MIKE

A macro would work, sure, I just want it to find the first right code. Any of the eight codes is fine, there's no preference which is why I thought putting it in a formula would work. You know, serach for the first code and if it isn't in 2A thru 2F, then search for the second code in 2A thru 2F. If nothing, the default would be the words "Search necessary."

Oh, I just realized I didn't say this--I want column G to show what code was found. It doesn't matter if it was in position one or 10 but column G should show TE207 (or whatever).

Does that make sense? Search 2a thru 2f for TE207. If nothing, then search 2a thru 2g for TE220A. If nothing search 2a thru 2g for TE201. If nothing, then default to "Search necessary." The position doesn't matter. If for instance, column A says TE207 and column C has TE220A, I don't care if TE220A is shown in G. I just need one code.

I thought by saying 'the first' it would convey there's no preference.

Sorry, I deleted one of my responses. I realized that the solution I offered was not going to work. A macro could be written, but I believe that I have a formula version that should work. Try this:

1 - Enter your list of 8 codes in a range and name the Range "Codes".

2 - Enter the following formula in G1 using

Ctrl-Shift-Enterto create an array formula.3 - Drag it down as far as you need to.

The formula will search A1 for any of the codes in your list. If #N/A is returned, it will then search B1, then C1, etc. If it finds the code a cell, it will stop and return the code. If A1:F1 all return the #N/A error, then "Search Necessary" will be returned.

=IFERROR(INDEX(Codes,MATCH(TRUE,ISNUMBER(SEARCH(Codes,A1)),0)),

IFERROR(INDEX(Codes,MATCH(TRUE,ISNUMBER(SEARCH(Codes,B1)),0)),

IFERROR(INDEX(Codes,MATCH(TRUE,ISNUMBER(SEARCH(Codes,C1)),0)),

IFERROR(INDEX(Codes,MATCH(TRUE,ISNUMBER(SEARCH(Codes,D1)),0)),

IFERROR(INDEX(Codes,MATCH(TRUE,ISNUMBER(SEARCH(Codes,E1)),0)),

IFERROR(INDEX(Codes,MATCH(TRUE,ISNUMBER(SEARCH(Codes,F1)),0)),

"Search Necessary"))))))

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Try this: The SEARCH() function does not work across multiple cells, so to get around this

Concatenate columns A thru F into one long string so your data looks like:

A B C D 1) TE207 TE220B TE220B TE220B TE220B TE220B TE207 TE207 2) TE207 TE220B TE220B TE220B TE220B TE220B TE207 TE220A 3) TE207 TE207 TE207 TE220B TE220B TE220B TE207 4) TE206 TE206 TE207 TE207 TE207 TE207 TE207 5) TE220A TE220B TE220B TE220B TE220B TE220B TE220A 6) TE206 TE206 TE206 TE206 TE206 TE220A TE220A 7) TE206 TE207 TE207 TE207 TE220B TE220B TE207In column D enter the codes you wish to search for.

Next give your list a Defined Name, I have use the nametlistThen in cell B1 enter the

ARRAYformula:=IFERROR(INDEX(tlist,MAX(IF(ISERROR(SEARCH(tlist,A1)),-1,1)*(ROW(tlist)-ROW($D$1)+1))),"No Match")

Don't forget to use CTRL-SHIFT-ENTER for the array.

Drag down as many rows as needed and you should get

the same as above.MIKE

Here is an improved version where you do not have to combine your data. With your data like:

A B C D E F G H I 1) TE207 TE220B TE220B TE220B TE220B TE220B TE207 TE207 2) TE207 TE220B TE220B TE220B TE220B TE220B TE207 TE220A 3) TE207 TE207 TE207 TE220B TE220B TE220B TE207 4) TE206 TE206 TE207 TE207 TE207 TE207 TE207 5) TE220A TE220B TE220B TE220B TE220B TE220B TE220A 6) TE206 TE206 TE206 TE206 TE206 TE220A TE220A 7) TE206 TE207 TE207 TE207 TE220B TE220B TE207In column I enter the codes you wish to search for.

Next give your list a Defined Name, I have use the nametlistThen in cell G1 enter the

ARRAYformula:=IFERROR(INDEX(tlist,MAX(IF(ISERROR(SEARCH(tlist,CONCATENATE(A1," ",B1," ",C1," ",D1," ",E1," ",F1))),-1,1)*(ROW(tlist)-ROW($I$1)+1))),"No Match")

Don't forget to use CTRL-SHIFT-ENTER for the array.

Drag down as many rows as needed and you should get

the same as above.MIKE

Nice formula. Just tossing out an FYI...

With Google Sheets and Excel for Office 365, the CONCATENATE portion could be replaced with:

TEXTJOIN(" ",1,A1:F1)

It worked with Sheets in Google Docs. I don't have Office 365 to test.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03

Ask Your Question

Weekly Poll

Do you think SpaceX laying off some of its workforce will help it succeed?

Discuss in The Lounge

Poll History