Conditional Formatting with duplicates

  • Thread starter Thread starter hayley
  • Start date Start date
H

hayley

Hi there,

I have a list of part numbers sorted by part number order.

Is there a formula in conditional formatting to highlight each batch
of alternate parts?
eg.
123644 -yellow
123644 -yellow
123644 -yellow
123644 -yellow
123656 - grey
123656 - grey
123656 - grey
155614 -yellow
155614 -yellow
155614 -yellow
155614 -yellow

Any help would be appreciated.

Thanks !
 
hayley > said:
Hi there,

I have a list of part numbers sorted by part number order.

Is there a formula in conditional formatting to highlight each batch
of alternate parts?
eg.
123644 -yellow
123644 -yellow
123644 -yellow
123644 -yellow
123656 - grey
123656 - grey
123656 - grey
155614 -yellow
155614 -yellow
155614 -yellow
155614 -yellow

Any help would be appreciated.

Thanks !
Click to expand...

I'm not sure if this is quite what you are looking for. This formula
=(COUNTIF($A$1:A1,A1)>1)
used in conditional formatting for the whole of column A will format
subsequent occurrences that are duplicates of a preceding one. That is, in
your example data, entries 2, 3, 4, 6, 7, 9, 10 and 11 will be highlighted
(as 1, 5 and 8 are the first occurrences of each value).
 
I have a list of part numbers sorted by part number order.

Is there a formula in conditional formatting to highlight each batch
of alternate parts?
eg.
123644 -yellow
123644 -yellow
123644 -yellow
123644 -yellow
123656 - grey
123656 - grey
123656 - grey
155614 -yellow
155614 -yellow
155614 -yellow
155614 -yellow
Click to expand...
...

If you mean you want to use conditional formatting to create contrasting color
bands for alternating groups of identical part numbers, it can be done. If your
entire table were named TBL, then you need to use the following conditional
formatting *FORMULAS* (i.e., formula conditions).

'Odd' group bands formatted as yellow:
=MOD(SUMPRODUCT(1/COUNTIF(OFFSET(TBL,0,0,SUM(ROW()-CELL("Row",TBL),1),1),
OFFSET(TBL,0,0,SUM(ROW()-CELL("Row",TBL),1),1))),2)>0.5

'Even' group bands formatted as grey:
=MOD(SUMPRODUCT(1/COUNTIF(OFFSET(TBL,0,0,SUM(ROW()-CELL("Row",TBL),1),1),
OFFSET(TBL,0,0,SUM(ROW()-CELL("Row",TBL),1),1))),2)<0.5

Note: I'm using >0.5 and <0.5 rather than =1 and =0 on purpose - my own light
testing showed that the SUMPRODUCT expression is subject to floating point
rounding error, so the MOD result can return very small positive numbers on rare
occasions - >0.5 and <0.5 are robust.
 
Hi Hayley,

Another way to do this is with a macro.


The macro would "think" like you do when doing this by hand.

It would colour the first row, then check if the next row should be
same or different.

If the same, colour it and move on to the next.

If different, change pen, colour it and move on to the next.

This can be repeated for as many rows as you have on the sheet.

Another beauty of this method is that you could use as many different
colours as you wish, and have the colour based on a feature of the
part number or any other field in the row.

David

Explore and Enjoy
 
Hi David

I've only done a few basic macros. Can you help in how I should run
this as I've tried but it only repeats the row I have coloured. Its not
thinking like you said.

Thanks

Hayley
 
Another way to do this is with a macro.
Click to expand...
...

The one drawback being that changes in the data wouldn't automatically trigger
changes in the formatting. The user would have to rerun the macro. Or Change and
Calculate event handlers would need to call the macro, thus effectively slowing
down entry and recalculation.
Another beauty of this method is that you could use as many different
colours as you wish, and have the colour based on a feature of the
part number or any other field in the row.
Click to expand...

Only the first part (> 4 colors) requires macros. The second is provided by
conditional formatting limited by the maximum length of conditional formatting
formulas. But those could call udfs, so there's as little limit on the
sophistication of conditional formatting as there would be for a macro-driven
approach.
 
Back
Top