Conditional format multiple columns, again.

[Alan C]

I asked this question several days ago and received little response, so let me see if I can clarify the question further.

I’m using Excel 2007 and have a worksheet with 35 columns and approx 5,000 rows. I want to use a conditional format to highlight data in only 4 specific columns that, if duplicated in any of the same 4 columns on the sheet will fill the cell with red fill.

That is, if the data in the 4 columns is duplicated, in all 4 columns, not just 2 or 3 out of the 4, those cells will be highlighted in red. The columns are D, AA, AC and AE. Just as an afterthought, I was thinking of sorting the columns to make these columns adjacent to one another.

I don’t know if this is a MATCH or a SUMPRODUCT function or a combination of both or for that matter any of these. I trust this is more understandable and as usual, any help will be greatly appreciated.

Thanks,

Alan

 

[3link]

I'm kind of confused. Let me see if I understand you correctly. Within these four columns, if all four share the same value for any particular row, you want them all to have the same formatting? If that's all you need, I think that's easy to do. I'll be glad to explain it if you can confirm that this is what you're looking for.

 

[Alan C]

Thanks for the response. Yes, in these 4 columns, if the data is the same, in all 4 columns, the conditional format will be triggered and will fill the cells with the color red. Will not format if the 4 conditions (duplicates) are not met.

Thanks again,

Alan

 

[3link]

Ok. I know a way to pull this off. There may be a simpler way, but this isn't all that time consuming anyway.

So we know that your columns are D, AA, AC and AE. First thing I want you to do is highlight all of the cells that contain data in column D.

Next, click on conditional formatting (under the home tab). Click on "new rule". You'll see something that looks like the image below.

Select the option "Use a formula to determine which cells to format". Type the following (this assumes the data begins in row one).

=AND($D1=$AA1, $D1=$AC1, $D1=$AE1)

Once this is typed, click on the format button and format it however you want it to look when all rows are duplicate.

Now I want you to repeat the same steps for the remaining three columns. However, you will be putting a different code in each column. Here they are:

Column AA

=AND($AA1=$D1, $AA1=$AC1, $AA1=$AE1)

Column AC

=AND($AC1=$D1, $AC1=$AA1, $AC1=$AE1)

Column AE

=AND($AE1=$D1, $AE1=$AA1, $AE1=$AC1)

That should do the trick. Let me know if you need any more help.

 

[Alan C]

Thanks,3Link! I'll try this right now and see if it works. I appreciate the help, but in any event will post the outcome here.

Fingers crossed and thanks again,

AC

 

[Alan C]

Hi 3Link,

I just can't seem to get this to work, although I understand the logic. Are those spaces after the comma's? I tried with spaces and without with no luck.

I have to close for today, but I'll check in the morning to see if you've replies.

Thanks for your patience.

AC

 

[3link]

Hi 3Link,

I just can't seem to get this to work, although I understand the logic. Are those spaces after the comma's? I tried with spaces and without with no luck.

I have to close for today, but I'll check in the morning to see if you've replies.

Thanks for your patience.

AC

When you get back tomorrow, please tell me which row the data starts in.

 

[Alan C]

Hi 3Link,

The data starts in row 2 with the headings in row 1. I've tried various combinations and even tried on some dummy data where the columns are adjacent to each other with no success. It's one of these issues that make your head explode. Any ideas, please let me know.

Thanks,

AC