Conditinal Format

[Bob]

Using Excel 2007. I have a workbook with 40,000 rows of data. Each row is
a set of data for a Bill_ID. There are several thousand Bill_ID's and the
number of rows associated with each Bill_ID varies. So, one Bill_ID may be
comprised of 6 rows, the next Bill_ID has 20 rows, etc. I'd like to
conditional format every other Bill_ID for easier identification / viewing.
Any suggestions? Thanks for your assistance.

 

[Pete_UK]

What form does your Bill_IDs take? Are they simple numbers, or
mixtures of alpha and numeric? Will the rows for one Bill_ID be
consecutive?

In Excel 2003 and earlier you can only have 3 conditional formats
(plus normal format) in one cell, so you would need to "transform" the
Bill_ID to 1 of 4 values maximum. This can be done numerically if the
IDs are numbers, or via a lookup table of unique IDs if they are text
values. Post back with the answers to the questions if you need
further guidance.

Hope this helps.

Pete

 

[Bob]

The Bill_ID's are formatted as General. An example is 1579148-0003. The
rows for each Bill_ID will be consecutive.
I'm using Excel 2007.

Thanks for the assistance!

 

[Pete_UK]

I don't have Excel 2007, so some of the menu options I describe might
have changed.

First of all, you can get a unique list of Bill_IDs by highlighting
them (plus the heading - you need a header) then click Data | Filter |
Advanced Filter. In the pop-up you should click on Unique Records
only, and Copy to another location - choose a blank column (let's say
L1) for the location and then click OK - you will now have unique IDs
in column L with your header in row 1.

Then highlight your Bill_IDs again (without the header) and click on
Format | Conditional Format - in the pop-up you should select Formula
Is rather than Cell Value is, and enter this formula:

=MOD(MATCH(A2,L$2:L$10000,0),2)=0

then click on the Format button, Patterns Tab (i.e. background
colour), and choose a colour, eg bright yellow. OK your way out. I
have assumed that your IDs are in column A, starting in A2, and that
you have up to 10,000 of them in column L - adjust the formula to
suit.

You should now find that the first IDs are not coloured, the second
are, the third are not etc.

Hope this helps.

Pete

 

[Bob]

Works great, but I'd like to highlight the entire row. Tried changing it
some by inserting Row() before the Match, but couldn't get it to work.
Thanks again for all your help.

 

[Pete_UK]

Glad it works - in your first post you said you only wanted the IDs to
stand out.

Select A2 and change the formula to this:

=MOD(MATCH($A2,$L$2:$L$10000,0),2)=0

Then you can use the Format Painter to apply that format to other
cells (across or down), or you could select all the cells in advance
and then use this formula.

Hope this helps.

Pete

 

[Bob]

All is working as desired! Thanks again for your help.

 

[Pete_UK]

You're welcome, Bob - thanks for the speedy feedback.

Pete