Conditionnal Formatting
 

Hi,

I have a sheet with 3 columns.
A is the list of 2500 references
B is the list of 6500 lot numbers
C is the quantity per lot per reference
I would like to change the format whenever I change the reference. Example

ref 1, Lot A, Qty 6
ref 1, Lot B, Qty 1
ref 1, Lot C, Qty 2
ref 5, Lot D, Qty 9
ref 5, Lot E, Qty 7
ref 2, Lot F, Qty 6,
ref 3, Lot G, Qty 8

I would like all lines of ref 1 to be displayed in blue, then ref 5 with no
colors and then ref 2 in blue and then ref 3 with no colors etc...
Please help.

Kind regards,

Marouane

Conditionnal Formatting
 

Excel Dubai wrote:
Hi,

I have a sheet with 3 columns.
A is the list of 2500 references
B is the list of 6500 lot numbers
C is the quantity per lot per reference
I would like to change the format whenever I change the reference. Example

ref 1, Lot A, Qty 6
ref 1, Lot B, Qty 1
ref 1, Lot C, Qty 2
ref 5, Lot D, Qty 9
ref 5, Lot E, Qty 7
ref 2, Lot F, Qty 6,
ref 3, Lot G, Qty 8

I would like all lines of ref 1 to be displayed in blue, then ref 5 with no
colors and then ref 2 in blue and then ref 3 with no colors etc...
Please help.

Kind regards,

Marouane


One way...

Assuming your data starts in A2, select the entire range starting with A2,
select Format / Conditional Formatting, select "Formula Is" and enter the following:

=MOD(SUM(IF(FREQUENCY(MATCH($A$2:$A2,$A$2:$A2,0),M ATCH($A$2:$A2,$A$2:$A2,0))0,1)),2)

Then select the alternating format you want.

Conditionnal Formatting
 

Glenn wrote:
Excel Dubai wrote:
Hi,

I have a sheet with 3 columns.
A is the list of 2500 references
B is the list of 6500 lot numbers
C is the quantity per lot per reference
I would like to change the format whenever I change the reference.
Example

ref 1, Lot A, Qty 6
ref 1, Lot B, Qty 1
ref 1, Lot C, Qty 2
ref 5, Lot D, Qty 9
ref 5, Lot E, Qty 7
ref 2, Lot F, Qty 6,
ref 3, Lot G, Qty 8

I would like all lines of ref 1 to be displayed in blue, then ref 5
with no colors and then ref 2 in blue and then ref 3 with no colors
etc...
Please help.

Kind regards,

Marouane


One way...

Assuming your data starts in A2, select the entire range starting with
A2, select Format / Conditional Formatting, select "Formula Is" and
enter the following:

=MOD(SUM(IF(FREQUENCY(MATCH($A$2:$A2,$A$2:$A2,0),M ATCH($A$2:$A2,$A$2:$A2,0))0,1)),2)


Then select the alternating format you want.



Actually, that will fail if your "ref #" repeats. Try this instead:

=MOD(SUMPRODUCT(($A$2:A2<$A$1:A1)*1),2)

Conditionnal Formatting
 

Glenn wrote:
Glenn wrote:
Excel Dubai wrote:
Hi,

I have a sheet with 3 columns.
A is the list of 2500 references
B is the list of 6500 lot numbers
C is the quantity per lot per reference
I would like to change the format whenever I change the reference.
Example

ref 1, Lot A, Qty 6
ref 1, Lot B, Qty 1
ref 1, Lot C, Qty 2
ref 5, Lot D, Qty 9
ref 5, Lot E, Qty 7
ref 2, Lot F, Qty 6,
ref 3, Lot G, Qty 8

I would like all lines of ref 1 to be displayed in blue, then ref 5
with no colors and then ref 2 in blue and then ref 3 with no colors
etc...
Please help.

Kind regards,

Marouane


One way...

Assuming your data starts in A2, select the entire range starting with
A2, select Format / Conditional Formatting, select "Formula Is" and
enter the following:

=MOD(SUM(IF(FREQUENCY(MATCH($A$2:$A2,$A$2:$A2,0),M ATCH($A$2:$A2,$A$2:$A2,0))0,1)),2)


Then select the alternating format you want.



Actually, that will fail if your "ref #" repeats. Try this instead:

=MOD(SUMPRODUCT(($A$2:A2<$A$1:A1)*1),2)


Correction...

=MOD(SUMPRODUCT(($A$2:$A2<$A$1:$A1)*1),2)