conditional formatting to format first-of-a-kind within a column 2

[electricpete]

I have a table in excel sorted by a column - call it serial number. Every serial number has many rows that go with it... so you see serial number repeated several times.

To make it easier on the eye I would like to distinguish the first appearance of a serial number with conditional formatting.

The logic would be something like: shade this cell if and only if it is different than the cell above. Unfortunately conditional formatting doesn't seem to be able to handle that type of comparsion, at least I haven't gotten it to.

Can anyone tell me how? (no, I don't want to create a new column or alter my data in any way, just the formatting).

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[chichuck]

electricpete,

I'm a little confused from your post what your table looks like. If it looks like this:

Col. Head A Col Head B Col. Head C
111 111 111
111 111 111
111 222 222
222 222 222
333 222 333
333 222 333

(Each column is sorted independently from all others by row, so those serial numbers are all in ascending (or descending) order, & are in groups, that is there is no 111 at the bottom of a column). I think you can get what you want.

Go to first cell in first col.
go to menu item format/conditional formatting
from diaog box, select value is.
next control, select from dropdown box not equal to
in the last box you see something like $c$4. That is a global cell ref. Edit out the two $ and make it a relative cell ref.
Click okay
copy the format from this cell to your entire table using
Edit/Copy and then Edit/Paste/Special/Formats.

You should get the first of each group of serial nos in each col to format as you like.

If your list of serial numbers in each col is not sorted (& grouped) then this won't work too well, it will use the format you set up every time the serial no. changes.


Regards,

chichuck

 

[BML]

electricpete, I've used the following on many similar types of spreadsheets. I sort the spreasheet based on the serial number column and then add a column for "Sequence". In that column I put a formula to check if the adjacent cell is the same as the one above it. If it is, the result is the sequence number from above plus 1. If not, the sequence number is 1.

Example: in cell A2 =IF(B2=B1,A1+1,1)

Then you can put conditional formatting on column B with a formula: in cell B2 =IF(A2=1,TRUE,FALSE)

This should allow you to specially format the first entry of a sequence assuming they are sorted.

If the entries cannot be sorted, you could probably make some sort of convoluted lookup and row match formula to see if the adjacent cell is the first time that value appears in the table.

Hope this helps!

 

[electricpete]

Thanks guys. chichuck gave me what I needed. I was trying to use conditional formatting with the "formula is" options rather than the "cell value is" option and it didn't seem to work. "cell value is" combined with "not equal to" and referenced to the cell above (with absolute $ edited out) did the trick. Then copied format with format painter.

BML - I have done what you suggest before but I like the idea of using format better because it doesn't clutter up my spreadsheet as much and keeps me more flexible.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.