0

I have a table in which every column describes a product and every row is responsible for one attribute of the product. I want to highlight cells which are duplicated in every column. I have for instance a table:

            x   a   b   a
            a   x   x   b
            c   b   c   x

And I need to highlight only cells with "x"-value which is present in every columns. But simple duplicates conditional formatting (like this one ) will highlights all of the cells, because every value has a duplicate in the entire table.

Any suggestions?

Edit not from OP to add clarification/image from comments elsewhere

Products are the columns themselves and in rows there are attributes of the product. By duplicate I mean a value (any row) in every column. Most of the attributes of the products are the same but located in different rows. I cannot sort them and arrange for example in the alphabetical order because it will turn the whole data structure to a mess. Here is an example of what I want to get: https://docs.google.com/spreadsheet/ccc?key=0Ah55-OVCGmw_dDJpMG9qMHhsLTBwbWlxMVQzdHBGYVE#gid=0. As you can see, all the values that are common for all columns are highlighted:

SU601579 example

2 Answers 2

1

You may want to use a helper column.

Starting with your data sample in cell A1, use a formula in helper column E, starting in E1

=A1&B1&C1&D1

Then use conditional formatting to see if there are any duplicates of the values in column E. You can conditionally format columns A to D and hide column E to keep the workbook clean.

The formula in helper column E can be adjusted to your needs. Also, the conditional formatting formula can be customised. I'm not completely sure I understand your requirements for flagging duplicates. You may want to post a data sample with some manually colored cells to help identify your final output.

Edit: looking at the question from some different angle, I thing you REALLY need to post a data sample and manually hilight the duplicates. It's not clear from your question what constitutes a duplicate. If products are in columns, what is in the rows? If A2 has an "x" and B7 has an "x" is that a duplicate? If so, why? If not, what is a duplicate?

Please explain your data architecture.

Edit after further comments:

I get the same results as the data sample with conditional formatting using the formla

=COUNTIF($1:$1048576,A1)>1

You can reduce the range to the cells that actually contain your products and their properties.

Actually, it's not quite the same result. There is a "no" in the data sample in cell C12 that is not highlighted. But "no" occurs in other columns, so it should be highlighted, I think.

I wonder what the value of this is when some of the properties are just "no" or several properties have the value "1" several times in one product. There is no way of telling which of the three"1" properties of product one and two are duplicates of the four "1" properties in product three. But your data sample highlights all the "1" cells, and so does my suggested conditional format.

enter image description here

Here is the formula in the CF Manager

enter image description here enter image description here

3
  • Products are the colums itselves and in rows there are atributes of the product. By duplicate I mean a value which contains in every column. Most of the atributes of the products are same but located in the different rows. I cannot sort them and arrange for example in the alphabetical order because it will turn the whole data structure to the total mess. Here is an example of what I want to get: docs.google.com/spreadsheet/… As you can see, all the values that are common for all colums are highlighted.
    – kvaleev
    May 31, 2013 at 6:34
  • Your method unfortunately doesn't do exact what is needed, because as I understand will be highlighted same values only between adjacent cells.
    – kvaleev
    May 31, 2013 at 6:34
  • Thanks for the data sample. I have updated my answer with new content.
    – teylyn
    May 31, 2013 at 9:26
0

You can use this formula in the rule:

=(IFERROR(MATCH(A1,$A:$A,0),0))*(IFERROR(MATCH(A1,$B:$B,0),0))*
 (IFERROR(MATCH(A1,$C:$C,0),0))*(IFERROR(MATCH(A1,$D:$D,0),0))>0

[I'm assuming that 'duplicate' means that a value appearing in column A also appears in all the columns B, C and D]

EDIT: You don't need iferror() after all ^^

=MATCH(A1,$A:$A,0)*MATCH(A1,$B:$B,0)*MATCH(A1,$C:$C,0)*MATCH(A1,$D:$D,0)>0
3
  • Oh, and you apply this to the whole range.
    – Jerry
    May 30, 2013 at 10:38
  • I cannot implement the formula, because an error is thrown in that part: MATCH(A1,$A:$A,0)
    – kvaleev
    May 31, 2013 at 6:39
  • @Konstantin Huh, which version are you using? I'm currently on 2007 and there's no issue with error. Might be it'll work if you use ; instead of ,? (Note, I wrongly put >1 in my initial formula which I just corrected to be >0.)
    – Jerry
    May 31, 2013 at 6:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .