1

I have a number of rows within a large spreadsheet that have some identical rows I would like those rows highlighted but only if every cell on the row matches, if random cells from different rows match, but not every cell on the row, the cell / row should be ignored.

Example of what I would like

So here you can see the desired effect Rows 191 and 192 are identical and have been highlighted. However even though the data in cells B190-B193 are the same, as is the data in cell N191-N193. The data in Cells that do not full match the other cells in their row have not been highlighted.

Using conditional formatting and other attempts mark any duplicate field and not ones where the entire row is identical.

Would appreciate any help, totally stuck!

  • If you're able to mark duplicates on a single field using conditional formatting, would it be satisfactory to concatenate all of the column data at the end of each row in a new column, then to a conditional format on that new column? – dangowans Sep 4 '19 at 20:25
  • @dangowans If A1 is 1 and A2 is 11 and B1 is 11 and B2 is 1. wouldn't the concatenations columns be be equal but the rows are actually different? – Forward Ed Sep 7 '19 at 17:17
  • @ForwardEd True. A unique delimiter between the values would fix that though. – dangowans Sep 9 '19 at 13:07
0

This looks quite hideous but works:

enter image description here

Steps:

  • Select A1:D4 in my example:
  • Apply new conditional formatting rule through formula:

    =COUNTIFS(INDIRECT("A1:A"&COUNTA($A:$A)),$A1,INDIRECT("B1:B"&COUNTA($B:$B)),$B1,INDIRECT("C1:C"&COUNTA($C:$C)),$C1,INDIRECT("D1:D"&COUNTA($D:$D)),$D1)>1
    
  • Apply condidional formatting and press OK


Why not just COUNTIFS with full column ranges instead of the volatile INDIRECT ranges? Because this is going to be CPU heavy I would opt for a longer formula isntead of one that will constantly check the entire column (for who knows how many columns you want to implement).

Can't INDIRECT hurt? Not per se, since conditional formatting is volatile on it's own. Therefor I would go with a quick COUNTA on the range instead. And possibly even throwing in multiple MATCH function to get the last used row is faster.

=COUNTIFS(INDIRECT("A1:A"&MATCH("ZZZ",$A:$A)),$A1,INDIRECT("B1:B"&MATCH("ZZZ",$A:$A)),$B1,INDIRECT("C1:C"&MATCH("ZZZ",$A:$A)),$C1,INDIRECT("D1:D"&MATCH("ZZZ",$A:$A)),$D1)>1

It didn't become pretty but at least it got faster :)

  • Thank you, this worked very well – Thomas Sep 10 '19 at 7:52
0

One way is to use conditional formatting with SUMPRODUCT:

=SUMPRODUCT(--($A:$A=$A1),--($B:$B=$B1),--($C:$C=$C1),--($D:$D=$D1))>1
  • Would COUNTIFS(...) > 1 be more efficient calculation-wise? – Jeeped Sep 4 '19 at 19:10
  • It might very well be. I have a hunch that there must be a more efficient way of doing this, but I can't seem to figure it out. – cybernetic.nomad Sep 4 '19 at 19:21
  • I tend to get a little shakey when I see SUMPRODUCT with full column cell references. – Jeeped Sep 4 '19 at 19:27
  • Technically you could shorten it up by eliminating the "--" and replacing the "," with "*". but it will still do the same thing in the end. – Forward Ed Sep 6 '19 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.