Apply Conditional Formatting based on Names

Topic

New Reply

Hi

I have a list of alphabetically last names, first names and they are sorted.
I’ like to apply the Bold or not Bold conditional format as follow :

All last names startin with letter A = BOLD
Going down, names start with B = not bold
Then last name starting with letter C may not be in the list :
If last names start with C are present then =BOLD
If not, then D=BOLD.
And so on till Z
I’ve been trying more than a dozen formula in the CF… but nothing works.

TIA

Reply | Quote

Replies

Francis

There is a way of doing this using only one Conditional Formatting rule.

Assume your list of names is in column A, highlight the column and then in

[*]Format
[*]Conditional Formatting

Change the drop down to Formula Is

and paste this formula into the box:

=MOD(CODE(LEFT(A1,1)),2)

Change the format to bold.

I have attached a workbook to show

Reply | Quote

=MOD(CODE(LEFT(A1,1)),2)

Change the format to bold.

That works only so long as you have names with all initial letters, and the request was (in effect) IF B is normal and there are ‘C’s, make Cs bold and Ds normal, but if there are no ‘C’s, make Ds bold and Es normal etc.

I would do it by having a hidden column B, with code like this from cell B2 down.
=IF(LEFT(A2)=LEFT(A1),MOD(B1,2),MOD(B1+1,2))
and use that column to control the conditional formatting

What it does is to alternate the hidden value between 0 and 1 every time the initial letter changes.

Amended file attached.

I’m not sure I actually like it much, because it’s I’m not sure how well it’ll work if you’re adding and removing entries on the list

Reply | Quote

That works only so long as you have names with all initial letters, and the request was (in effect) IF B is normal and there are ‘C’s, make Cs bold and Ds normal, but if there are no ‘C’s, make Ds bold and Es normal etc.

I would do it by having a hidden column B, with code like this from cell B2 down.
=IF(LEFT(A2)=LEFT(A1),MOD(B1,2),MOD(B1+1,2))
and use that column to control the conditional formatting

What it does is to alternate the hidden value between 0 and 1 every time the initial letter changes.

Amended file attached.

I’m not sure I actually like it much, because it’s I’m not sure how well it’ll work if you’re adding and removing entries on the list

Hi

Thanks for your effort. I don’t know much about the MOD function…guess I need to read and play around
with this function more to understand it.
Agreed with you on the last point, I ‘ll highlight this to the users to manage expectation.

Reply | Quote

Thanks for your effort. I don’t know much about the MOD function…guess I need to read and play around

MOD is just a mathematical function that returns the remainder when one number is divided by another. I’m just using as a way of getting two alternating values using addition only.

If the previous hidden value is zero, adding 1 gives 1, and the remainder when divided by 2 is 1.
If the previous hidden value is 1, adding 1 gives 2, so the remainder when divided by 2 is 0.

Reply | Quote

Francis

There is a way of doing this using only one Conditional Formatting rule.

Assume your list of names is in column A, highlight the column and then in

[*]Format
[*]Conditional Formatting

Change the drop down to Formula Is

and paste this formula into the box:

=MOD(CODE(LEFT(A1,1)),2)

Change the format to bold.

I have attached a workbook to show

Hi Jerry

Thank you very much on this, although not exactly producing the result but your solution
providing or facilitates to the correct solution by Gfamily

Like I have mentioned, I am unfamiliar with the MOD function and need to play around with this more
to understand it better.

Reply | Quote