Conditional Filtering

[carl]

I am trying to highlight rows in my data to make it possible to spot
patterns. For example, in the table below is it possible to highlight the
rows when there are 5 consecutive identical values in ColA and ColB. In the
table, rows 4,5,6,7,8 would be highlighted but rows 9,10,11,12,13 would not
be.

Thank you in advance.

Row ColA ColB
1 ABC 123
2 DEF 456
3 HIJ 789
4 ABC 123
5 ABC 123
6 ABC 123
7 ABC 123
8 ABC 123
9 DEF 456
10 DEF 456
11 DEF 456
12 DEF 456
13 DEF 123

 

[Biff]

Hi!

Here's one method you could try.

You need a helper column for each column that you want to compare. In your
example you're comparing 2 columns so you need 2 helper columns. Also, there
should be an empty row above the start of your data. So, if your data starts
in row 2 and row 1 is empty:

Based on Harlan Grove's formulas for counting consecutive cells:

In C2 enter this formula and copy across to D2:

=IF(A3<>A2,1,"")

In C3 enter this formula and copy across to D3:

=IF(A4<>A3,ROWS(C$2:C3)-IF(COUNT(C$2:C2),LOOKUP(2,1/ISNUMBER(C$2:C2),ROW(C$2:C2)-1),0),"")

Select both C3 and D3 and copy down to the end of your data.

Now, since you wanted to identify 5 consecutive entries and based on your
sample data, cells C9 and D9 should equal 5.

Now, use conditional formatting.

Select the rows in which your data resides. In your example this would be
rows 2:14.

Goto Format>Conditional Formatting
Formula is: =AND($C2=5,$D2=5)
Select a format style. Maybe a background fill color.
OK out.

This will only highlight a single row. Maybe if I had more time I could
figure out how to highlight all 5 rows but this will at least get you headed
in the right direction.

Biff

 

[Biff]

Here's a more refined method.....

You can use either cf to highlight the rows or filter on the criteria of how
many consecutive instances you wish.

Still requires the row above your data being empty and still requires 2
helper columns.

If you don't want these helper formulas visible you can either move them of
screen to say columns IU and IV or just hide the columns these formulas are
in.

In C2 enter this formula:

=IF(A2&B2=A1&B1,C1+1,1)

In D2 enter this formula:

=IF(A2&B2=A3&B3,D3,C2)

Select both C2 and D2 and copy down to the end of your data.

Now, if want to use conditional formatting.....

If you want to highlight rows where consecutive instances are 5, enter 5 in
some cell, say, J1.

Conditional Formatting
Formula is: =AND($J$1<>"",$D2=$J$1)

This will highlight all 5 rows.

Now, if you want to highlight consecutive instances of, say, 3 or 4, would
you also want consecutives of 5 or 6 to highlight? If so, change the cf
formula to:

=AND($J$1<>"",$D2>=$J$1)

Or, you could use an autofilter and filter on column D = 5 (or whatever).

Biff