Re: Conditional Filtering
- From: "Biff" <biffinpitt@xxxxxxxxxxx>
- Date: Thu, 9 Jun 2005 20:58:50 -0400
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
"Biff" <biffinpitt@xxxxxxxxxxx> wrote in message
news:uh4sccSbFHA.1312@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
> "carl" <carl@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:CFAC4A03-A200-4D0C-931D-1F68BC320698@xxxxxxxxxxxxxxxx
>>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
>>
>
>
.
- References:
- Conditional Filtering
- From: carl
- Re: Conditional Filtering
- From: Biff
- Conditional Filtering
- Prev by Date: Any Samples???
- Next by Date: excel 2002
- Previous by thread: Re: Conditional Filtering
- Next by thread: rolling time period
- Index(es):
Relevant Pages
|
