How to highlight duplicate values in concatenation of two columns – Excel 2007

Imagine you have two columns of data – one contains sector (A; B; C; D), the other one contains a position in a sector (1..14) and you want to highlight duplicates. For some reason you cannot just have both information in one columns, so you can’t use pre-set rule for Conditional Formatting in Excel.

[singlepic id=11 w=202 h=139 float=]

In the table above you want the values in row 1 and row 4 to be highlighted, because the concatenation of column A and B is duplicate (value „A1“). First you will have to create another column, that will contain concatenation of columns A and B. So let’s create column C with formula =CONCATENATE(A1; B1).

[singlepic id=16 w=420 h=147 float=]

Next we have to create a custom Conditional Formatting rule on column A (all rows of it) with following formula:

=COUNTIF($C$1:$C$4; CONCATENATE(A1; B1)) > 1

[singlepic id=13 w=621 h=457 float=]

This rule will search the range C1:C4 (modify with respect to the number of your rows in a table) for value of concatenation of columns A and B and if a value is found more than once, rule will apply. Next you have to set formatting for the rule – I use red text color. Now you’ll have table, that will have contents of column A colored in red, if there are any duplicates. For the above table it would look like this:

[singlepic id=14 w=220 h=117 float=]

We would of course want to highlight duplicate values in both columns, so we will have to add another rule for column B. The formula will remain the same, as will the formatting:

[singlepic id=15 w=221 h=123 float=]

Et voilà! Now we can just hide the column „C“ and you are done.

Tento příspěvek byl zařazen do kategorie Jak udělám?. Do oblíbených si uložte trvalý odkaz.

Napsat komentář

Vaše emailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *