Conditional Formatting and Autofilter

[ak_edm]

Hi,

I've setup excel to take the alphabetical records I have (1 row = 1 record)
and perform a calculation on each row that eventually makes the spreadsheet
appear with a green or white background for each differnt name. Column A
contains the last name of the person in each record. So...

A B C Z
1 LAST FIRST INVOICE ........
2 Kirkpatrick Lady 72772 1
3 Jones Mister 72554 2
4 Jones Mister 72555 2
5 Smith Guy 73301 3

In some column off-screen like column Z in each row starting with row 2
where the first record is, I tell Excel to determine if the last name above
the current row is the same as the last name in the current row. If the
names are different then add 1 to the number above itself (in column Z). If
the names are the same then carry the number above itself. (we dont have so
many clients that i need to worry about the first name check yet)

The formula for cell Z2 is : =IF(A2<>A1,Z1+1,Z1)
The formula for cell Z3 is : =IF(A3<>A2,Z2+1,Z2)
etc...

This works very well with conditional formatting for the rows, making the
odd numbers in the cell produce a green pattern background and the even
numbers staying with a white background.

My problem is when autoformat is turned on and some of the rows disappear.
The calculation STILL calculates the hidden rows and messes up the
green/white/green/white altering pattern. Any way to make the calculation
perform on only the visible rows?

 

[T. Valko]

This is rather complicated and calculation intensive so it might slow things
down if you have many rows of data.

This eliminates the column Z formulas.

Assumes no empty cells in the column A range.

Assume the range to format is A2:C20.

Select the *entire* range A2:C20 starting from cell A2. Cell A2 will be the
active cell. The active cell is the one cell in the selected range that is
not shaded. The formula will be relative to the active cell.

Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:

=MOD(COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET($A$2:$A2,ROW(A$2:A2)-ROW(A$2),0,1)),MATCH($A$2:$A2,$A$2:$A2,0)),ROW(A$2:A2)-ROW(A$2))),2)

Click the Format button
Select the Patterns tab
Select a shade of green
OK out

Note: if you test this formula **on the worksheet** it has to be array
entered.

Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER

I wonder if this falls into the category of being "too clever"? <inside
joke>

 

[ak_edm]

Holy Cow!

I thought I could get carried away when it came to nested formulas, but you
out there. And whatever you did, it works! I cant even begin to wrap my
head around what the equation does.

You're right though it is slow. On my 420-some rows of data it crawls when
it has to calculate at around row 100 and greater.

An amzing formula though. I'm going to step through it and see whats going
on. Very nice piece of work!

Thank you.

 

[T. Valko]

The formula is doing a progressive SUBTOTAL count of the unique entries in
column A then testing the result to see if it's an even or odd number..
Since this is working with a filtered range we have to step through the
range 1 row at a time hence the use of the SUBTOTAL function.

Let me see if I can make it faster. I'll play around with it later this
evening when I have more time.

 

[T. Valko]

I came up with something that is significantly faster *however* it requires
the use of an add-in.

If you can download and install the free add-in, Morefunc.xll, from:

http://xcell05.free.fr/morefunc/english/index.htm

Alternate download site:

http://www.download.com/Morefunc/3000-2077_4-10423159.html

Then, using a helper column, like column Z, enter this array formula** in Z2
and copy down as needed:

=COUNTDIFF(IF(SUBTOTAL(3,OFFSET(B$2:B2,ROW(B$2:B2)-ROW(B$2),0,1)),B$2:B2),FALSE,FALSE)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Then, use this as the CF formula:

=MOD($Z2,2)

This is a lot faster than the previous method but it's still not "instant".
Calculation will still take about 0.5 to 1 second. (tested on 500 rows of
data)