Conditionally format rows based duplicates only found in first column? Then Filter.

[RyanF]

Hi All,

I’ve seen a couple of threads that have started me in the right direction but I can’t figure out how to apply them to my situation. This will be a big info dump and if anyone sees an alternative solution that is obvious and I've missed then I'm all ears. I'm trying to conditionally format rows of data based on duplicates in the first column, then filter the results. I have a table of data with mutiple variables assigned to different "headings" that looks a little something like this:

	A	B	C	D	E
1	Ref #	Heading	Variable 1	Variable 2	Department
2	0001	Alpha	Nil	Nil	AAA
3	0002	Beta	XXX	12	AAA
4			YYY	13
5			ZZZ	02
6	0003	Gamma	XXX	09	BBB
7			YYY	21

<tbody>
</tbody>

The data then gets filtered using Excel’s normal built-in Filter function by putting the drop-downs across the header row.

You might be able to see where this is going…

If I filter column C for Variable 1 = YYY I’m going to get

	A	B	C	D	E
1	Ref #	Heading	Variable 1	Variable 2	Department
4			YYY	13
7			YYY	21

<tbody>
</tbody>

When I need to see this instead:

	A	B	C	D	E
1	Ref #	Heading	Variable 1	Variable 2	Department
4	0002	Beta	YYY	13	AAA
7	0003	Gamma	YYY	21	BBB

<tbody>
</tbody>

Merged cells are the work of the devil and it’s a requirement of the client to

1. Have the data formatted like the first table
2. Use the normal Excel filter function

I’ve written a macro that fills in the blanks on the table to look like this:

	A	B	C	D	E
1	Ref #	Heading	Variable 1	Variable 2	Department
2	0001	Alpha	Nil	Nil	AAA
3	0002	Beta	XXX	12	AAA
4	0002	Beta	YYY	13	AAA
5	0002	Beta	ZZZ	02	AAA
6	0003	Gamma	XXX	09	BBB
7	0003	Gamma	YYY	21	BBB

<tbody>
</tbody>

[selects the table, then GoTo Special selects the blanks, fills in the blanks with the cell above, then Copy and Pastes As Values the entire table again to fill everything in]

I’m stuck on how to input a formula into the conditional formatting window that will:
Format the text to white in columns A, B, and E based on there being a duplicate above that row in Column A only. i.e. conditionally format the values with a * below:

	A	B	C	D	E
1	Ref #	Heading	Variable 1	Variable 2	Department
2	0001	Alpha	Nil	Nil	AAA
3	0002	Beta	XXX	12	AAA
4	0002*	Beta*	YYY	13	AAA*
5	0002*	Beta*	ZZZ	02	AAA*
6	0003	Gamma	XXX	09	BBB
7	0003*	Gamma*	YYY	21	BBB*

<tbody>
</tbody>

If I use the conditional formula I found: =A1=A2, then cell E3 gets made white text when I don’t want it to, hence the “referencing column A” part of the question (Column A is always a unique ID number whereas Column E can have a duplicate in the row above). [Edit: Why can't I type Enter or put a line break here... I'll try re-edit at home...] When I go to filter on Column C for YYY again, the conditional formatting needs to realise to un-white the text, which is my next headache because it means conditional formatting that acts relative to hidden rows as a result of a filter... Oh and did I mention the client wants this done in 2003? This is an afterthought though – as I can force them to use 2010 if need be

 

[RyanF]

Beyond page 15. Trying a bump

 

[RyanF]

Found a solution to the conditional formatting part of my question. Conditionally formatting cell A3 to the formula =$A3=$A2 then copying the format across works. I'm still left with the second problem though:

How can I make conditional formatting treat hidden rows relatively (format based on the cell directly above it, treating a hidden row as if it is not there)

OR

How do I temporarily disable conditional formatting while auto-filter is in place

 

[Cool blue]

Hi, investigate the subtotal function. The return value is zero when the cell is hidden.

 

[RyanF]

Hi, investigate the subtotal function. The return value is zero when the cell is hidden.

That is genius. If the cells I was checking for duplicates were in a number format I could use this. Unfortunately, it's a string of letters and numbers (unlike in my original example).

 

[Cool blue]

No problem... helper columns

 

[Marcelo Branco]

Maybe...

Select A2:B7 and in CF use this formula

=AND(SUBTOTAL(3,$A:$A)=COUNTA($A:$A),$A2=$A1)
Font --> white

Do the same for E2:E7

Applying filter...

	A​	B​	C​	D​	E​
1​	Ref #​	Heading​	Variable 1​	Variable 2​	Department​
4​	0002​	Beta​	YYY​	13​	AAA​
7​	0003​	Gamma​	YYY​	21​	BBB​

<TBODY>
</TBODY>

M.