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

R

RyanF

New Member
Joined
Oct 23, 2012
Messages
23
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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Ref #
[/TD]
[TD]Heading
[/TD]
[TD]Variable 1
[/TD]
[TD]Variable 2
[/TD]
[TD]Department
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]0001
[/TD]
[TD]Alpha
[/TD]
[TD]Nil
[/TD]
[TD]Nil
[/TD]
[TD]AAA
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]0002
[/TD]
[TD]Beta
[/TD]
[TD]XXX
[/TD]
[TD]12
[/TD]
[TD]AAA
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]YYY
[/TD]
[TD]13
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]ZZZ
[/TD]
[TD]02
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]0003
[/TD]
[TD]Gamma
[/TD]
[TD]XXX
[/TD]
[TD]09
[/TD]
[TD]BBB
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD]YYY
[/TD]
[TD]21
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Ref #
[/TD]
[TD]Heading
[/TD]
[TD]Variable 1
[/TD]
[TD]Variable 2
[/TD]
[TD]Department
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]YYY
[/TD]
[TD]13
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD]YYY
[/TD]
[TD]21
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

When I need to see this instead:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Ref #
[/TD]
[TD]Heading
[/TD]
[TD]Variable 1
[/TD]
[TD]Variable 2
[/TD]
[TD]Department
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]0002
[/TD]
[TD]Beta
[/TD]
[TD]YYY
[/TD]
[TD]13
[/TD]
[TD]AAA
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]0003
[/TD]
[TD]Gamma
[/TD]
[TD]YYY
[/TD]
[TD]21
[/TD]
[TD]BBB
[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Ref #
[/TD]
[TD]Heading
[/TD]
[TD]Variable 1
[/TD]
[TD]Variable 2
[/TD]
[TD]Department
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]0001
[/TD]
[TD]Alpha
[/TD]
[TD]Nil
[/TD]
[TD]Nil
[/TD]
[TD]AAA
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]0002
[/TD]
[TD]Beta
[/TD]
[TD]XXX
[/TD]
[TD]12
[/TD]
[TD]AAA
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]0002
[/TD]
[TD]Beta
[/TD]
[TD]YYY
[/TD]
[TD]13
[/TD]
[TD]AAA
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]0002
[/TD]
[TD]Beta
[/TD]
[TD]ZZZ
[/TD]
[TD]02
[/TD]
[TD]AAA
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]0003
[/TD]
[TD]Gamma
[/TD]
[TD]XXX
[/TD]
[TD]09
[/TD]
[TD]BBB
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]0003
[/TD]
[TD]Gamma
[/TD]
[TD]YYY
[/TD]
[TD]21
[/TD]
[TD]BBB
[/TD]
[/TR]
</tbody>[/TABLE]

[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]
Click to expand...
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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Ref #
[/TD]
[TD]Heading
[/TD]
[TD]Variable 1
[/TD]
[TD]Variable 2
[/TD]
[TD]Department
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]0001
[/TD]
[TD]Alpha
[/TD]
[TD]Nil
[/TD]
[TD]Nil
[/TD]
[TD]AAA
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]0002
[/TD]
[TD]Beta
[/TD]
[TD]XXX
[/TD]
[TD]12
[/TD]
[TD]AAA
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]0002*
[/TD]
[TD]Beta*
[/TD]
[TD]YYY
[/TD]
[TD]13
[/TD]
[TD]AAA*
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]0002*
[/TD]
[TD]Beta*
[/TD]
[TD]ZZZ
[/TD]
[TD]02
[/TD]
[TD]AAA*
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]0003
[/TD]
[TD]Gamma
[/TD]
[TD]XXX
[/TD]
[TD]09
[/TD]
[TD]BBB
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]0003*
[/TD]
[TD]Gamma*
[/TD]
[TD]YYY
[/TD]
[TD]21
[/TD]
[TD]BBB*
[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Last edited:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
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
 
Upvote 0
Cool blue said:
Hi, investigate the subtotal function. The return value is zero when the cell is hidden.
Click to expand...

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).
 
Upvote 0
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...


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]
Ref #​
[/TD]
[TD]
Heading​
[/TD]
[TD]
Variable 1​
[/TD]
[TD]
Variable 2​
[/TD]
[TD]
Department​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
0002​
[/TD]
[TD]
Beta​
[/TD]
[TD]
YYY​
[/TD]
[TD]
13​
[/TD]
[TD]
AAA​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]
0003​
[/TD]
[TD]
Gamma​
[/TD]
[TD]
YYY​
[/TD]
[TD]
21​
[/TD]
[TD]
BBB​
[/TD]
[/TR]
</TBODY>[/TABLE]


M.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Conditional Format to highlight dupes on EACH column BUT only on visible filtered selection
Replies
4
Views
420
felixstraube
F
O
  • Solved
VBA - Filter columns, cut/paste rows new sheet, pivot table
2
Replies
12
Views
261
Skyybot
Skyybot
J
  • Solved
conditional formatting based on duplicate cells
Replies
4
Views
237
Joe4
Joe4
M
  • Question Question
Conditional Format Columns Only When Filter Applied
Replies
1
Views
348
kevin9999
K
T
  • Question Question
Flattening a hierarchy from 5 columns into a unique one
Replies
1
Views
725
Kerryx
K

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top