Multiple condtional formatting

[DocBrown]

I'm trying to combine two conditional format concepts I found here.

1. Odd and Even Banding,
2. Hide duplicates values in column.

I can make them work independently, but I have a column where I want to have
the banding taking place and to hide the duplicates. Is there a way to make
both happen?

For the desired cells, I have the Hide duplicates as condition 1, and
Banding as condition 2. When I reversed the, the Banding overroad the Hide
Duplicates. It appears that the first condition met is the one that takes
affect.

Actually, Here's a more detailed description of what I'm doing. I'm trying
to keep track of items purchased, and there can be multiple items per
receipt. I want to make the items that are on one receipt to be 'grouped' or
highlighed so I can see which items were on a particular receipt. I'd like
the grouping/highlighting to be semi automatic, where I don't have to muck
with the format menus and such. Maybe set a char in another column that can
indicate the group?

The Hide duplicates was intended to 'hide' the duplicate purchase date to
show the group.

Any suggestions?

Thanks,
John

 

[Shane Devenshire]

Hi,

What version of Excel are you using?

How are you applying banding?

How are you hiding duplicates?

Since I don't know what you did, here is one solution. Assume there is data
in all the cells in column A. Add a new column to your filtered? area, I
will add this to the range is B1:B20 enter the following formula in the
range
=SUBTOTAL(2,A$2:A2) (you can hide this column later, if necessary)
Apply the following conditional format to the range:
1. Select the range you want to format.
2. Choose Format, Conditional Formatting
3. Pick Formula is from the first drop down
4. in the second box enter the formula
=MOD($B2,2)=1
5. Click Format and apply the color you want for the banding

Now when you apply the filter? that hides duplicates the alternate rows will
be banded.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[DocBrown]

Excel version 2002 SP2, part of Office XP.

The banding is applied by the following formula for the conditional
formatting in ALL the cells that make up my data area:

=MOD(ROW()-14,5*2)+1>5
where the 14 is the first data row, and 5 is the number of rows in the band.
The conditioanl format applied is to change the cell shading to a color of my
chosing.

The Hide duplicates is accomplished with the following formula:

=IF(B14<>"",B14=B13)

Where row 14 is my first data row.

(of course, since the forumla is relative, each row has the appropriate row
number.)

This formula is applied to the cells of column 'C' in my data area. The
conditional format applied is to set the foreground color to the same color
as the cell shading to make the text 'disappear'. I select the rows by
putting a character in the 'B' column of rows where I want the duplicate to
be hidden. This causes the following to occur:

B C
----------
x At
x At <- hidden
x At <- hidden
y All
y All <- hidden
y All <- hidden
All
All

I hope this is clear.

John

 

[DocBrown]

I've solved the problem!!

The trick is that there are three conditional formats in play.
1. change the font color,
2. change the shading color.
3. change both.

The condition listed first is important. The conditions used are:
1. Change both: =AND(<shading formula>, <font color formula>)
2. Change font: =IF(B14<>"",B14=B13)
3. Change shade: =MOD(ROW()-14,5*2)+1>5

Where 14 is the first row of my data, and the '5' in the shade formula is
the number of rows in the shade band.

Now in my case I chose to have the font color change based on a character
the user enters in column B so the user could control which cells are to be
blanked. These conditions are placed in the cells for column C. If you want
to have the blanking take place solely on a change in value say in column C,
the formula is simply:

=C14=C13

where 14 is the first row of your data.

Have fun.
John