Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
How to create icon sets for excel 2007 conditionnal formating ? | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
expanding custom formatting without removing existing cell formatting? | Excel Worksheet Functions | |||
How to program a macro to hide rows with conditionnal formating | Excel Discussion (Misc queries) |