Formula to find text pattern across multiple cells in a row.

[whiteheadbell]

HI,
I am wanting to create a formula in excel that will search for a pattern in text across multiple cells on the 1 row. I have 50 cells in the range (all on the 1 row) that will either have a Y or an N in each cell. I want excel to be able to search this range of cells to look for the first instance of the pattern Y, N, N, N. Each cell will only have 1 letter in it, so it needs to look at all the cells for the pattern. The pattern could possibly be repeated in the same row, so it needs to identify the first instance of this pattern. Once it identifies the cell with the 'Y' in it prior to the 3 consecutive cells with 'N' in them, I want it to return the value of the cell in row 1 of that same column.

Basically, I am trying to analyse the results of a spelling test. Once the student gets 3 incorrect results in a row, the test is over and the last correct word places them at the right level for further spelling practice. The number of the word on the spelling test then correlates with an alphabetised list of words. So for example, if the 10th word was the last correct word before 3 consecutive errors, this would place the student halfway through grade 2 academically and assign them list 'I' to provide appropriate spelling words to practice and learn. This is easy to see if it is just a few students that I am analysing, but I need to be able to do this quickly for several hundred students.

I have tried using the SEARCH, FIND, MATCH, IF, LOOKUP functions in excel, but I can't get it to search for the pattern across multiple cells, only individual cells. I haven't even tried the second part of the formula to return the value of row 1 when it finds the Y, and I am hoping someone out there is clever enough to help me figure this one out. I have attached a binary version of my spreadsheet if that helps.

formula request ynnn.xlsb

Thanks and sorry for long post.

[DocAElstein]

Hello whiteheadbell,
Welcome to excelfox, I didn’t find your post long, - its better to give a full explanation as you did. :-)

Formulas are not my strong point.
I think I can see a way to do this. It may not be the best way.
The formula ideas that I have result in a long formula. This is usually an indication that it is a bad way to do it.

Anyway… if you simply put all the text from the cells together ( called concatenating ) , then you can use Find on that concatenated string . If I understand correctly, Find returns the “position along counting from the left” of the start character of what it is looking for in the character string that it is given to look in. That seems to be coincidentally the number that you are looking for.
( I guess if you actually wanted to get what is in the cell in that first row then you would use the Index function with that found position along, or VLookUp somehow )

( The formula is not very nice to write in manually, but I did not have to. It is quite easy using VBA coding to make a formula like that, so that is what I did. )

I used the range BB4 – BD5 to make the formulas

See if this is a help

_____ Workbook: formula request ynnn.xlsb ( Using Excel 2007 32 bit )

Row\Col	BB	BC	BD
4	yyyyynynyyynnynnnynnnnynynnnnnnnynnnnynnnnnnnnnnnn	14	14
5	yyyyyyyyyyyyynynyyyynyynnyynnnnnnnnnnnnnnyynynnnnn	27	27

Worksheet: List 1

_____ Workbook: formula request ynnn.xlsb ( Using Excel 2007 32 bit )

Row\Col	BB	BC	BD
4	=B4 & C4 & D4 & E4 & F4 & G4 & H4 & I4 & J4 & K4 & L4 & M4 & N4 & O4 & P4 & Q4 & R4 & S4 & T4 & U4 & V4 & W4 & X4 & Y4 & Z4 & AA4 & AB4 & AC4 & AD4 & AE4 & AF4 & AG4 & AH4 & AI4 & AJ4 & AK4 & AL4 & AM4 & AN4 & AO4 & AP4 & AQ4 & AR4 & AS4 & AT4 & AU4 & AV4 & AW4 & AX4 & AY4	=FIND("ynnn",BB4)	=FIND("ynnn",B4 & C4 & D4 & E4 & F4 & G4 & H4 & I4 & J4 & K4 & L4 & M4 & N4 & O4 & P4 & Q4 & R4 & S4 & T4 & U4 & V4 & W4 & X4 & Y4 & Z4 & AA4 & AB4 & AC4 & AD4 & AE4 & AF4 & AG4 & AH4 & AI4 & AJ4 & AK4 & AL4 & AM4 & AN4 & AO4 & AP4 & AQ4 & AR4 & AS4 & AT4 & AU4 & AV4 & AW4 & AX4 & AY4)
5	=B5 & C5 & D5 & E5 & F5 & G5 & H5 & I5 & J5 & K5 & L5 & M5 & N5 & O5 & P5 & Q5 & R5 & S5 & T5 & U5 & V5 & W5 & X5 & Y5 & Z5 & AA5 & AB5 & AC5 & AD5 & AE5 & AF5 & AG5 & AH5 & AI5 & AJ5 & AK5 & AL5 & AM5 & AN5 & AO5 & AP5 & AQ5 & AR5 & AS5 & AT5 & AU5 & AV5 & AW5 & AX5 & AY5	=FIND("ynnn",BB5)	=FIND("ynnn",B5 & C5 & D5 & E5 & F5 & G5 & H5 & I5 & J5 & K5 & L5 & M5 & N5 & O5 & P5 & Q5 & R5 & S5 & T5 & U5 & V5 & W5 & X5 & Y5 & Z5 & AA5 & AB5 & AC5 & AD5 & AE5 & AF5 & AG5 & AH5 & AI5 & AJ5 & AK5 & AL5 & AM5 & AN5 & AO5 & AP5 & AQ5 & AR5 & AS5 & AT5 & AU5 & AV5 & AW5 & AX5 & AY5)

Worksheet: List 1

Alan

P.s. For large amounts of data, a VBA coding solution is usually regarded as better. Lots of long formulas can make a workbook unnecessarily large and possibly slow

P.s. 2 I think there is a CONCATENATE function, and for higher versions of Excel the CONCAT function, which can simplify the formula a bit

[whiteheadbell]

Thanks DocAEIstein,

After spending too many hours testing formulas that I thought should work with the If and AND functions I flippantly asked my cousin (who just happened to call) if she knew how to work with excel spreadsheets. After laughing at my stupid question, she put her 17 year old on the phone, who together with her 15 year old brother, very quickly identified the easiest way to make it work. I emailed her my document and she set up a shadow sheet with the following formula:

=IF(AND('List 1'!B4="y",'List 1'!C4="n",'List 1'!D4="n",'List 1'!E4="n"),'List 1'!B1,"")

This basically says that if B4 on my original sheet is a Y, and c, d and e4 are all N's, then return the value in cell B1 on my original sheet into the cell where this formula is typed on the shadow sheet. I then copied this along the whole row, and then copied the whole row down the page. Fortunately the value in the cells in row 1 of my original sheet were all ascending from left to right, as I ended up with 2 or even 3 returned values across the row. So to narrow it down further, I then added a formula at the end of the row to look for the smallest value that wasn't a 0. That formula was:

=SMALL(B4:AY4,COUNTIF(B4:AY4,0)+1)

This then just returned the smallest number in the row which is the data I was trying to isolate in my spreadsheet.

While it is not the neatest formula, and it requires a shadow set, it did do the trick. I could then go back into my original sheet (List 1) and do: =('shadow'!ba4) to populate the value created by the messy formula.

Most importantly though, it works (even though I do now feel old, and 2 teenagers think I am hopeless and computer illiterate!)

Anyway, thank you so much for your suggestion. I appreciate your assistance.

[DocAElstein]

Hello whiteheadbell,

Thanks for adding/ describing your solution.

I prefer solutions that use another worksheet sometimes, as I can see what’s going on easier . I think a lot of professionals seem to think you should do that sort of thing only if you really need to-
But I am not a professional. I am a very late starter with anything to do with computing and only started with the damn things to help speed up an important personal project.
I frequently get annoyed with the things, but I got a bit hooked on Excel and in particular Excel VBA so, in the Winter months , one of my pass times is a bit of forum participation.

If you do a lot more Excel, one thing I would really recommend is forcing yourself to learn a bit of VBA coding. It sounds very daunting, at least it did to me, so I avoided it for a long time.

I am really annoyed with myself now , as so many things are much easier to do with coding. In particular for large worksheets, you can do so much more , or you can do with a short piece of coding something which would need a massive amount of large formulas
( Everything you need to do VBA you already have. It is already there. You don’t have to do anything in the way of installing. You are just not using it. Its like having a massive amount of extra stuff on your car like all wheel drive, turbo boosters and god knows what else, and all you do is sit in the car when its raining To a first approximation, Excel itself is “made” with a bit of running VBA coding, pre written which kicks in when you “open” Excel. The amount of coding is infinitesimally little compared with what you could further add to the program and use )

Alan

[p45cal]

In cell BA4, array-enter this formula (array-enter means committing the formula to the sheet using Ctrl+Shift+Enter, not just Enter):
=INDEX($B$1:$AV$1,MIN(IF($B4:$AV4 & $C4:$AW4 & $D4:$AX4 & $E4:$AY4="ynnn",COLUMN($A4:$AU4))))
Then copy down.
Just one snag: if the ynnn pattern is not found anywhere in the row it still returns 1, but to circumvent that you could use the longer (still array-entered):
=IF(SUMPRODUCT(--($B6:$AV6 & $C6:$AW6 & $D6:$AX6 & $E6:$AY6="ynnn"))>0,INDEX($B$1:$AV$1,MIN(IF($B6:$A V6 & $C6:$AW6 & $D6:$AX6 & $E6:$AY6="ynnn",COLUMN($A6:$AU6)))),"not found")

If you have a more recent version of Excel you might be able to use:
=INDEX($B$1:$AY$1,SEARCH("ynnn",TEXTJOIN("",FALSE, $B4:$AY4)))
but this is more liable to being fooled with empty cells and/or cells with not exactly one character in.

[p45cal]

Of course, one realises later how one's overcomplicated things; a normally-entered formula in BA4 seems to work well:
=IFERROR(INDEX($B$1:$AV$1,MATCH("ynnn",INDEX($B4:$ AV4&$C4:$AW4&$D4:$AX4&$E4:$AY4,,),0)),"not found")