Check specific range for any value that repeats itself 3 subsequent times

**blackmilk** · 03-21-2013, 12:18 PM

Hi,

I would appreciate it if you guys can help me.

I'm looking for a way to have excel (2007) tell me if - within a certain range of cells (in a row) - there happens to be 3 (or more) subsequent cells that contain the same value.

I'm looking to have something like this:

b.jpg

For example, if A2:J2 is a range I would like to check, I want the formula in K2 to check this range for any value that repeats itself for three (or more) subsequent times.

Alternatively, if I can have excel mark the cells themselves and show me where the repetition occurs, that would be even better. Like this:

a.jpg

Thanks very much for anyone who can help!
David

**tigeravatar** · 03-21-2013, 12:39 PM

David,

Attached is an example workbook based on the criteria you described.
In order to accomplish what you're looking for, I used helper rows which are in columns M:V
M2, M3, and M4 are all simply the number 1
In cell N2 and copied over and down to V4 is this formula:

Please Login or Register  to view this content.

With the helper rows setup, in cell K2 and copied down is this formula:

Please Login or Register  to view this content.

The workbook also has the conditional formatting if you'd like to use that instead. The conditional format applies to range $A$2:$J$4 and uses this formula:

Please Login or Register  to view this content.

**ChemistB** · 03-21-2013, 01:02 PM

A more complex solution without helper cells (I'd personally use the helper cells)

=IF(SUM(IF($A2:$H2=$B2:$I2, IF($B2:$I2=$C2:$J2,1,0))),"Yes", "No")
entered as an array (using CONTRL SHFT ENTER instead of ENTER. If done properly, you'll see {} around your formula.

**blackmilk** · 03-31-2013, 11:50 AM

Hi,

Thanks very much for your replies! And sorry for my late response.

This turns out to be a little more complicated than I expecetd! I actually have tons and tons of data: 341 variables X 362 subjects. I thought if I gave an example of 10 variables X 3 subjects, I would be able to generalize the solution to my dataset, but adding more than 100,000 helper cells seems a bit too much (I actually tried it and failed to make it work, but couldn't figure out why). And the second solution seems too complicated for this amount of data.

Any idea what I should do? I can upload the dataset if that can help.

Thanks much!
David