Conditionally Format duplicates in visible rows only

Hi pinebush.

I added a helper column in Col E and in E3 entered =AGGREGATE(3,7,A3) and copied down. This simply sets a flag if the cell is visible.

I then changed your CF to =AND(A$1="Critical",A3<>"",SUMPRODUCT(--EXACT(A$3:A$12,A3)*--($E$3:$E$12=1))>1)

It seems to work!

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

David, that is a good start, and is helpful to see your revised CF formula. I guess I neglected to say that I need to have the same thing happen on all the columns. Therefore I was hoping to avoid a helper column, because my actual spreadsheet contains over 30 columns, which means that I will need the same number of helper columns. It seems to be overkill, but I have not been able to come up with a better solution on my own, so I have come to the forum for help.

FDibbins,
The problem is not that some of the hidden rows may be highlighted. Rather the problem is that it gives a false positive to the remaining visible value, because some of it's dups are hidden. I want only the visible ones to be considered for possible dups.

You are not alone in wishing to avoid helper columns, but I use them all the time, because they make formulae less complex. It doesn't have to be in column e - you could have it in Col ZZ, or A or even on another sheet (and hide it). By the way, you only need one helper column. All it is doing is looking in column A to see if it is hidden or not. That's it! You don't need several columns as you assumed in your reply.

Have a look at C1. If you change it to Critical, the CF kicks in, and the cells will be highlighted, but if you filter on Spring, as you suggested, the formatting is back to default. This will work the same for all 30 columns when the CF is applied to the whole range.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

David,
I have incorporated your solution, and it works like a charm. I am not against using a helper column, but as you suggested, I assumed that I would need one for each table column.
Thanks for your professionalism, and clear instructions. I also appreciate the absence of sarcasm when I did not thoroughly test before challenging your first response.

You're welcome!

Please remember to click on the Add Rep star (bottom left) and to mark the thread as solved.

All the best, David