Finding frequency and repetition of values in cells in a row

Hi, everyone. I'm hoping someone here can give me a hand. I've been at this for over a week and I've tried everything I could think of. I'm not great with Excel, so I'm hoping there is a way to do what I want.

I have 10 cells in a row - A1-A10 for instance. Each cell will have a letter in it - the letters are determined from a list of about 8 possibilities. The letters I am concerned with are "R" and "D". What I need to be able to figure out is, from left to right (A1-A10) if a cell has an "R" in it, then a cell to it's right (anywhere in the row) has a "D". But I need to know if this happens twice. For instance, if the letters went like this:

R T T H R D F R T D - this works, because there is a string of "R D R D" in it.

R R R D T T H D R R - this doesn't work because there isn't an "R D R D" there.

I hope that makes sense. I've attached a simple file with letters in cells - and also another file with my many different attempts in it. If anyone has an idea, I'd love to hear it. Thanks in advance for any help.

Adam

Try this UDF solution:

Add the code to your VB editor (ALT+F11, Insert|Module)

Please Login or Register  to view this content.

Then apply formula in L4:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER

copied down... assuming you don't have blank rows between...

TRUE means the RDRD combination exists

Make sure that after you enter the first formula that the cell is still active (i.e. the cursor is still there... or just hit F2 to make sure) then hold the CTRL and SHIFT keys down and hit ENTER.. you should see { } brackets appear around the formula, then copy it down.

if the whole range in the alternate rows is blank, then use this formula in L4:

=IF(COUNTBLANK(B4:K4),"",ISNUMBER(SEARCH("RDRD",(aconcat(IF(ISNUMBER(MATCH(B4:K4,{"D","R"},0)),B4:K4,""),"")))))

confirmed again with CSE keys and copied down.

Here is your attachment with UDF and formula

Here it is again.

If you paste it into the cell, just hit F2 and then do the CSE entry.

Sorry, not sure what the problem is. Can you elaborate?

Try this in L4:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down.

Note: I added more functionality to only skip rows that have all cells blank and to include rows that have a few cells within blank....not sure if you wanted that or not, though...

You are welcome