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

[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