Copy conditional formatting to other rows

Question

I have a list of items, each with its own code, that I would like the user to rank in an arbitrary order of preference 1st to 5th. Not all items need to be ranked. However, each item should only be ranked ONCE (no duplicates). My sample table of records is as follows:

ID  1st 2nd 3rd 4th 5th

1   U74 L65 G56 N28 M82

2   N28 A11 L65 P37 L65

3   H72 R99 B42 Y95 G56

4   T63 C58 P37 B42 C58

(In this instance, user ID 2 and 4 have duplicate codes L65 and C58 respectively)

Checking and highlighting duplicate codes in a single row is easily done with conditional formatting. The problem I'm running into is how to "copy" the formatting to the rest of the rows. My actual dataset has over 300 records, so doing it manually for each row is not really an option.

score 2 · Accepted Answer · edited May 23 '17 at 12:15

2

If you have a data like this:

enter image description here

You just need to use this formula: =COUNTIF($B2:$F2,B2)>1
Of course you need to use Use a formula to determine which cell to format as a rule type:

enter image description here

And also you need to define where you'll apply the formatting ($B$2:$F$5 in our example).
Or you can select the entire range you want to format before actually adding the Conditional Format

enter image description here

The result would be:

enter image description here

Is this what you're trying?

Edit1: How it works? (for zipzit)

Let us examine the formula: =COUNTIF($B2:$F2,B2)>1
Which we applied in: $B$2:$F$5

Regular formula in Excel Cells behaves the same way when used in Conditional Formatting.
So absolute and relative cell address (with $ and without $) applies to it as well.
So if we apply the formula for example in B2 only, it will evaluate how many occurrence does the value in B2 have in $B2:$F2 using COUNTIF formula.

The answer is 1. In our formula, you'll only format it if it is >1.
Now say we copy it in C2.
Notice that B2 in our formula use relative referencing both on rows and column.
So instead of evaluating B2 again against $B2:$F2, it will evaluate C2.
Now, to make sure it will evaluate it against the same set of range $B2:$F2, notice that we precede the column address with $.

I have other post about Conditional Formatting which you might want to check out.

edited May 23 '17 at 12:15

Community

1
1

answered Mar 20 '15 at 04:37

L42

19,427
11
44
68

Its clever, but I was unable to get this to work beyond four rows. What is the logic of the formula? countif (range, criteria?) ... can you verbalize the test? We're looking for duplicates among each row's entries (for 3000 different rows...) – zipzit Mar 20 '15 at 04:48
@zipzit you'll need to change the range where the formatting applies. If you have 3000 rows then it should be applied to `$B$2:$F$3000`. – L42 Mar 20 '15 at 04:53
`=COUNTIF($B10:$F10,B10)>1` totally works, note the $ sign placement. I also found a reference, http://www.techrepublic.com/blog/windows-and-office/how-to-find-duplicates-in-excel-245163/ Had to go back to 2009. I still don't understand how that formula functions, but it appears to work. oops. that is the same formula you posted. Apologies.. I like your method, way better than mine. Upvote! – zipzit Mar 20 '15 at 04:59
@zipzit Ah of course. You'll have to adjust that as well. That is where you reference where you're Condition is based. I'll edit my post on how it works. – L42 Mar 20 '15 at 05:01

zipzit · Answer 2 · 2015-03-20T04:41:10.660

enter image description here

Ouch, you are right. There appears to be no easy way to copy the format to more than one row at a time. I've tried many different versions of the F4 trick, to no avail.

You're not going to like this answer, but I'd do it via macro, copy all the data to a new sheet tab, count the number of rows, then loop from top to bottom using a row pointer and paste "format only" one line at a time via the macro. Its not romantic, but it will work.

Another way to do it is to trick the code so you can do a math review (e.g.
vote #1 = 00000001 (U74) vote #00000010 (L65) vote #00000100 (G56) vote#00001000 (N28) vote#00010000 (M82) ... etc...

that way you'd just add the votes, and you should see 01011101 for a result. If you see 10021100 you immediately know there is a repeat. Not sure how many choices your folks have to vote for... You could actually do this with binary numbers (expressed as digital #'s then use Dec2Bin conversion on the sum to quickly see the result)

Copy conditional formatting to other rows

2 Answers2

Linked