×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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.

RE: conditional formatting to format first-of-a-kind within a column

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

RE: conditional formatting to format first-of-a-kind within a column

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!

RE: conditional formatting to format first-of-a-kind within a column

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close