Ask Your Question

Conditional Formatting - Cell value is duplicate

asked 2019-02-23 18:25:24 +0200

Giorgi gravatar image

updated 2020-08-07 20:08:51 +0200

Alex Kemp gravatar image

How can I achieve this formatting disregarding the capital case of first letter? i.e. cell value is duplicate with ignore-case?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-02-23 18:51:42 +0200

Say, you want to apply the formatting to column A. Use Formula is condition.

You may use formula like COUNTIF($A:$A; A1)>1. The COUNTIF comparison is case-insensitive (at least in my testing), which would disregard case of all characters.

But if you want to specifically disregard case of only the first character, then a computation-intensive solution like SUMPRODUCT(REPLACE($A:$A;1;1;UPPER(LEFT($A:$A;1)));REPLACE(A1;1;1;UPPER(LEFT(A1;1)))) > 1 could do.

Possibly @erAck could give a better advise :-)

edit flag offensive delete link more


"cell value is duplicate with ignore-case" is perfectly matched with the COUNTIF()>1 example given. But, caveat, COUNTIF() follows the wildcards/regex settings under Tools->Options->Calc->Calculate so may not produce the desired result if such meta characters are contained in the Criteria argument. To eliminate that this would be better suited: =SUMPRODUCT(A:A=A1)>1

erAck gravatar imageerAck ( 2019-02-25 14:52:48 +0200 )edit

Thank you, Mike. This worked. But the point is, again - usability and UX. I don't think I should be writing this kind of formulas in order to achieve simple thing. Can you phrase in words what this formula is doing? looks like takes some range.. but is the range $A to $A? and what is second argument for? didn't get it.

Giorgi gravatar imageGiorgi ( 2019-03-15 12:43:06 +0200 )edit

I don't even want to discuss if a train of thought like "whatever I am doing is a simple thing that must be doable without understanding application's basics" is a correct one... and if that train of thought should be the same for accountant, a mathematician, a PhD of statistics, and a string theory specialist.

The formula says that: "count how many cells in the entire fixed column A (which is the range defined by $A:$A) are equal case-insensitively to current cell (which is what relative referenceA1 stands for in the conditional formatting range that starts at A1); check if that count is greater than 1, which means current cell has another duplicating value somewhere".

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-15 13:07:03 +0200 )edit

Clear. The only one confusing point was A1 as it kind of refers to particular slot. So A1 gets the value of every single cell in iteration.

Giorgi gravatar imageGiorgi ( 2019-03-15 13:15:23 +0200 )edit

Mike, can you please enhance your answer by taking this: All works fine, but when I copy (say) few rows of one column from file A, and paste that into file B: 1. By using Paste, highlighting doesn't work (even if later I align the formatting of those pasted rows with the formatting of above ones); 2. By using Paste Special -> [unformatted] text, it works good (in both options).

Giorgi gravatar imageGiorgi ( 2019-03-21 10:36:34 +0200 )edit

When you paste cells somewhere, the cells are pasted with all the formatting they had in the original location. This means, that they also bring their conditional formatting with them, including when they don't have one. When you paste unformatted text, it naturally takes formatting of the target cells.

If you need to "align the formatting of those pasted rows", you might use Clone Formatting tool, or you might need to adjust conditional formatting range using its management dialog. What procedure you used when "aligned formatting" is untold :-)

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-21 12:37:36 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-02-23 18:25:24 +0200

Seen: 911 times

Last updated: Feb 23 '19