automatic background color in cells with similar data

[rohit43]

I, daily get a list of individuals with some data against each one of them. E.g Amit would appear 7 times in the list, John would appear 10 times in the list and so on and so forth. I am required to sort the data as per names and then fill one background color. One color for one person so that it becomes easy to read data specific to an individual.Ia there a way that the color fills up automatically. Pls note the names and number of entries are not same everyday.

[Pete_UK]

I would suggest that you just have alternate fill/no fill rather than different colours throughout the sheet. You could accomplish this by introducing a helper column which returns a sequential number for each person, and then using conditional formatting based on the numbers such that the fill is applied if the number is even, so odd-numbered people have no fill. Assuming names are in column A, starting in A2 with headers in row 1, and that you use column H as your helper column, you should leave H1 blank and use this formula in H2:

=IF(A2=A1,H1,H1+1)

then copy down to the bottom of your data. Then highlight all the cells starting from A2, click on Conditional Formatting | New Rule | Use a formula ... , then put this formula in the box:

=MOD($H2,2)=0

then click on the Format button | Fill tab and then choose your colour, then OK your way out.

Hope this helps.

Pete

[rohit43]

I am sorry it doesn't works with the advised formula. I am attatching a test file. Could you pls apply this on the file. Thanks!TEST.xls

[Pete_UK]

File attached. Your names are in column C, not column A (which the formula was based on).

Hope this helps.

Pete

[rohit43]

Thanks! It will solve the purpose even though it lets me fill only one color. But it is still better than filling colors manually.
Also, I was able to understand the role of =if formula. Could you please give me some description of the "=MOD($H2,2)=0". As in what is MOD and what does the rest of the arguments stand for. Thanks a ton.

[Pete_UK]

Well, the helper column (H) just sets up a sequential number for each name, i.e. 1, 2, 3, 4 etc., assuming the data is sorted by name, which you say you have to do anyway. The function MOD gives the remainder after division, so MOD(H2,2) is dividing H2 by 2, and this can only return 0 or 1 - 0 for even numbers and 1 for odd numbers. So, the CF condition is essentially saying that if the number in the helper column is even then apply the background colour (grey), but if it is odd then leave the background as it is (white). This then gives you the colour banding, although you could change the grey to another colour if you wish (if you overdo colours, though, it can look a bit garish).

Hope this helps.

Pete

PS. Please mark the thread as Solved if you consider it to be so. Also, you can click on the "star" icon at the bottom left corner of any post that you have found to be helpful, in order to pass on thanks more directly to a contributor (not only in this thread).

[rohit43]

Brilliant! One last question pls. How does it returns only 1 or 0. Because 1 or 3 dvd by 2 would not return 1 or 0. And what is meant by =0 at the end of formula

[Pete_UK]

Take the numbers 1, 2, 3, 4, and 5 - divide each by 2 and take the remainder. You get 1, 0, 1, 0, 1 and so on. The =0 at the end of the formula is picking out the even numbers. You could change it to =1 and that would pick out the odd numbers.

Hope this helps.

Pete

[rohit43]

Perfect! Thank u so much for ur help.