|
|
|
|
Hi agian,
This has higlighted the first row of all entries that have multiple rows
with the same value in the B col.
I'm sorry if I have not explained my self very well and I'm happy to let it
slide if it is to difficult to work out in this fashion.
cheers,
Linc
"David McRitchie" wrote:
Your expectations do not match your data , because Row 1 is unique
and the other unique rows you want to ignore. Such discrepancies make
debugging very difficult. But you did show me that Row 1 is not
always going to colored because it would only get colored if row 2 has the
same value.
Your selection will determine which cells get colored, it you want to color the
entire row then select all cell. If you just want to color within Column B then
select column B only.
Assuming we are checking Column B then this is the formula (make the window as wide as possible)
copy this formula for the Formula 1 condition -- no other conditions
=AND(NOT(ISBLANK($B1)), IF(ISERR($B1<OFFSET($B1,-1,0)),1,$B1<OFFSET($B1,-1,0)), $B1=OFFSET($B1,1,0) ).
If it is not column B you are checking then change the column to the column that is being checked.
When you enter the formula, the active cell must be on row 1.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Linc" wrote in message ...
Hi David,
Thanks very much for the help, I appreciate it.
Unfortunatly this still does not achieve the desired result.
My technical knowledge of excel has been well exceded by now so I cannot
give a technical response. I will try to re-explain.
In the data below, I am trying to make the rows with the following entries
in the second col. highlight. as1, 3dfg, sf5, ed7, 9rgf, nh11. This should be
every time the cell value changes, so does the highlighting.
I know the help you have been giving me works on the first col being the
values used, which I have tested, but the data dump will put this in the
second col.
Hope you can help.
cheers, Linc
Customer Name Sales Order Number Cust P/O or W/O Number
x as1 1
x as2 2
x 3dfg 3
x gf4 4
y sf5 5
y sf5 6
y sf5 7
y cbvg6 8
z ed7 9
z ed7 10
z ed7 11
z ed7 12
x ed7 13
x ed7 14
x ed7 15
x ed7 16
y vf8 17
y 9rgf 18
y qw10 19
y qw10 20
z qw10 21
z qw10 22
z qw10 23
x nh11 24
x re12 25
x re12 26
"David McRitchie" wrote:
Sorry about that, try this:
Color all unique rows (based on column A) with a light pastel color, and
Color the first row (based on Column A) of groups of rows with same value with a different pastel color.
Select cell A1
Select all cells, Ctrl+A
The active cell must be on Row 1 because formula is based on $A1 as reference
Condition 1 -- Formula is: =AND($A1<OFFSET($A1,-1,0),$A1<OFFSET($A1,1,0))
Condition 2 -- Formula is: =$A1<OFFSET($A1,-1,0)
The first condition will identify items that are unique (single item grouping)
The second condition will identify the first item of multiple item groups
To eliminate highlighting of the empty cells at end use
Condition 1 -- Formula is: =AND($A1<OFFSET($A1,-1,0),$A1<OFFSET($A1,1,0))
Condition 2 -- Formula is: =AND(NOT(ISBLANK($A1)),$A1<OFFSET($A1,-1,0))
If I misunderstood and it is okay to highlight the unque rows as well, use
(** this is the one that I would use**)
Condition 1 -- Formula is: =AND(NOT(ISBLANK($A1)),$A1<OFFSET($A1,-1,0))
None of the above will color row 1 you could fix that by assuming Row 1
would always be the start of a group, including a group of 1 and that you
have no header row.
Condition 1 -- Formula is:
=OR(ROW($A1)=1,AND(NOT(ISBLANK($A1)),$A1<OFFSET($ A1,-1,0)))
The comparison for the cell before Row 1 will result in an Error,
which is not a True condition so coloring would not be applied on an error
unless you add the OR condition.
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Linc" wrote
This almost worked. It seems to have highlighted the last row of any rows
with the same values as well as any rows that are the only ones of their
value.
|