Highlight two identical rows - user warning

[Ztv]

Hi guys
I have this Excel Sheet, which is regularly updated with additional data. One of the biggest mistakes in this process definately is if you add some data twice (or more).

Therefore, I would like to be warned if two rows are identical. I just don't know if I should use conditional formatting or some kind of a VBA-add in or any other macro. What would you suggest? Please find a dummy excel sheet attached.

Next problem: Two lines can actually be identical, without causing problems.

[Richard Buttrey]

Why are rows 4 & 6 identical?

Is it because EACH of the columns F:J are identical
Is it because ONE of the columns F:J are identical
Is it because the sum of the columns F:J are identical

[Ztv]

@Richard: Sorry. It's because EACH of the columns F:J are identical

[Richard Buttrey]

Hi,

I'd be inclined to use a couple of helper columns (B & C in the attached, C is an array formula entered with Ctrl-SHift-Enter, along with a conditional format to identify the rows.

If the layout doesn't change you could add the helper cells in advance and cover all the relevant monthly columns. Or if the layout changes and you need to make this flexible then you could use a macro to create the same helper columns and CF.

[Ztv]

@Richard: I like your solution, but it seems like the formula is only checking the first number in the row? Or am I missing something? For example: Try to change G4:J4 in your sheet.

[Richard Buttrey]

Hi,

I was attempting in C4 to create the equivalent of a Concat() function. Seemingly it's not logically consistent. Use the longer
=F4&G4&H4&I4&J4.....

[sktneer]

You may also try this.....
Select the range D4:K9 and use the formula given below to make a New Rule for conditional formatting and set the format as per your choice.

=AND($F4&$G4&$H4&$I4&$J4<>"",NOT(ISERROR(MATCH($F4&$G4&$H4&$I4&$J4,$F$3:$F3&$G$3:$G3&$H$3:$H3&$I$3:$I3&$J$3:$J3,0))))

[Ztv]

@Richard and sktneer: I do understand your suggestions, though it's gonna be a terrible long formula in my original sheet with more than 50 rows :/

[Richard Buttrey]

@Richard and sktneer: I do understand your suggestions, though it's gonna be a terrible long formula in my original sheet with more than 50 rows :/

Hi,

Why do you think that?
And did you mean 50 columns rather than rows. Even if you have 50 columns you want to concatenate then there are simple functions that you can use to create a long formula with 50 &s. I've just generated one in a few seconds.

And there's only one conditional format formula required which can then be applied to as many columns & rows as you wish

[sktneer]

While applying this formula to 50 rows of data for conditional formatting, does it make the sheet slow?
Well I know that there is some kind of Range().FormatConditions.Add property but I have never used it and very much unaware of using it.
Unless you get a proper code for conditional formatting as per your criteria, with a very limited knowledge of VBA, I have tried to write a code for worksheet change event and placed it on your sheet module. See if this is something you can work with.
The code will auto adjust if you add more months on row3, I have deleted "...." from cell K3 to make the code work properly.
Please find the attached sheet to see if this works as per your requirement. You may change the existing data to check whether the code is working correctly or not.

[Ztv]

@Richard: Sorry. I've clearly misunderstood the way I should use your formulas in the conditional formatting. I'll try both your solutions (and the VBA) and get back to you.