Hi All,

I would like to use Conditional Formatting (Excel 2003 Windows) to put a
Border around two cells in the same column. The second cell to be formatted
will always be directly beneath the first cell; i.e. cell I2 (1st) and I3
(2nd).

There are two consecutive rows for each Numeric Label - the Rank is the 1st
row and the Frequency the 2nd row. The Rank is listed twice in column "B" for
each Numeric Label for sorting purposes.
Each Rank in a row is unique.

The criteria to Conditionally Format the cells - Format Rank and related
Frequency:
1. Match Rank =10 (greater than or equal to 10) in the 1st row of the
relevant Numeric Label - CF is a Border. The Ranks to be matched may be in
any column between "E" and "I".

2. Put a Border around the cell in the 2nd row (Frequency) that corresponds
to the Numeric Label and is directly below the Matched Rank in the 1st row.

Sample Data Layout:
Columns: "A" = Numeric Label, "B" = Rank, "C" = Total, "D" = Text Labels, "E":
"I" = Rank & Frequency (6th-10th Position). The Data starts on row 2 with
Numeric Label 220. Data row 2 to 11.

Label Rank Total POS 6th 7th 8th 9th 10th
220 18 Total Rank 14 10 17 12 18
220 18 1041 Freq 7 6 5 3 3
470 16 Total Rank 6 15 13 17 11
470 16 1058 Freq 4 4 3 3 2
180 14 Total Rank 13 10 16 12 14
180 14 1042 Freq 6 5 4 3 3
400 13 Total Rank 11 14 5 23 13
400 13 1053 Freq 5 5 4 4 3
40 11 Total Rank 9 11 13 15 14
40 11 1040 Freq 6 5 4 4 3


Expected Results:
Label 220 - Row2 Column "I" Rank 18 & Row3 Frequency 3 should have an outline
Border.
Label 180 - Row6 Column "I" Rank 14 & Row7 Frequency 3 should have an outline
Border.
Label 400 - Row8 Column "I" Rank 13 & Row9 Frequency 3 should have an outline
Border.
Label 40 - Row10 Column "F" Rank 11 & Row11 Frequency 5 should have an
outline Border.

Thanks
Sam

--
Message posted via http://www.officekb.com

This would bring you close to your intents ..

Select E2:I11, then apply CF using the formula:
=OFFSET(INDIRECT("E"&INT((ROW(A1)-1)/2)*2+2),,COLUMN(A1)-1)=$B2
Format fill color to taste OK out

(I couldn't find a way to color the border around
each of the "2 cells" group in the manner that you wanted)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:71740450eb94c@uwe...
Hi All,

I would like to use Conditional Formatting (Excel 2003 Windows) to put a
Border around two cells in the same column. The second cell to be
formatted
will always be directly beneath the first cell; i.e. cell I2 (1st) and I3
(2nd).

There are two consecutive rows for each Numeric Label - the Rank is the
1st
row and the Frequency the 2nd row. The Rank is listed twice in column "B"
for
each Numeric Label for sorting purposes.
Each Rank in a row is unique.

The criteria to Conditionally Format the cells - Format Rank and related
Frequency:
1. Match Rank =10 (greater than or equal to 10) in the 1st row of the
relevant Numeric Label - CF is a Border. The Ranks to be matched may be in
any column between "E" and "I".

2. Put a Border around the cell in the 2nd row (Frequency) that
corresponds
to the Numeric Label and is directly below the Matched Rank in the 1st
row.

Sample Data Layout:
Columns: "A" = Numeric Label, "B" = Rank, "C" = Total, "D" = Text Labels,
"E":
"I" = Rank & Frequency (6th-10th Position). The Data starts on row 2 with
Numeric Label 220. Data row 2 to 11.

Label Rank Total POS 6th 7th 8th 9th 10th
220 18 Total Rank 14 10 17 12 18
220 18 1041 Freq 7 6 5 3 3
470 16 Total Rank 6 15 13 17 11
470 16 1058 Freq 4 4 3 3 2
180 14 Total Rank 13 10 16 12 14
180 14 1042 Freq 6 5 4 3 3
400 13 Total Rank 11 14 5 23 13
400 13 1053 Freq 5 5 4 4 3
40 11 Total Rank 9 11 13 15 14
40 11 1040 Freq 6 5 4 4 3


Expected Results:
Label 220 - Row2 Column "I" Rank 18 & Row3 Frequency 3 should have an
outline
Border.
Label 180 - Row6 Column "I" Rank 14 & Row7 Frequency 3 should have an
outline
Border.
Label 400 - Row8 Column "I" Rank 13 & Row9 Frequency 3 should have an
outline
Border.
Label 40 - Row10 Column "F" Rank 11 & Row11 Frequency 5 should have an
outline Border.

Thanks
Sam

--
Message posted via http://www.officekb.com

Select E2:I11, then apply CF using the formula:
Ensure E2 is the active cell in the selection E2:I11
(The suggested CF formula points to E2, and will be "propagated" from there
to the full range E2:I11)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi Max,

Thank you very much for your assistance. Your Formula does the job Great!

Cheers,
Sam

Max wrote:
=OFFSET(INDIRECT("E"&INT((ROW(A1)-1)/2)*2+2),,COLUMN(A1)-1)=$B2
Format fill color to taste OK out

Select E2:I11, then apply CF using the formula:
Ensure E2 is the active cell in the selection E2:I11
(The suggested CF formula points to E2, and will be "propagated" from there
to the full range E2:I11)

--
Message posted via http://www.officekb.com

Welcome, Sam. Good to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:717a171f598a0@uwe...
Hi Max,

Thank you very much for your assistance. Your Formula does the job Great!

Cheers,
Sam

I couldn't find a way to color the border around each of the "2 cells"
group in the manner that you wanted

Think you'd have to format every other row like 2,4,6 then 3,5,7 and set row
2,4,6 to have no bottom border and set row 3,5,7 to have no top border. I
didn't even think that's what he wanted but maybe it is since in his sample
data the borders don't really stand out and distinguish which set of numbers
is which.

Your formula works but trips on certain situations where there are empty
cells.

Biff

"Max" wrote in message
...
This would bring you close to your intents ..

Select E2:I11, then apply CF using the formula:
=OFFSET(INDIRECT("E"&INT((ROW(A1)-1)/2)*2+2),,COLUMN(A1)-1)=$B2
Format fill color to taste OK out

(I couldn't find a way to color the border around
each of the "2 cells" group in the manner that you wanted)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:71740450eb94c@uwe...
Hi All,

I would like to use Conditional Formatting (Excel 2003 Windows) to put a
Border around two cells in the same column. The second cell to be
formatted
will always be directly beneath the first cell; i.e. cell I2 (1st) and I3
(2nd).

There are two consecutive rows for each Numeric Label - the Rank is the
1st
row and the Frequency the 2nd row. The Rank is listed twice in column "B"
for
each Numeric Label for sorting purposes.
Each Rank in a row is unique.

The criteria to Conditionally Format the cells - Format Rank and related
Frequency:
1. Match Rank =10 (greater than or equal to 10) in the 1st row of the
relevant Numeric Label - CF is a Border. The Ranks to be matched may be
in
any column between "E" and "I".

2. Put a Border around the cell in the 2nd row (Frequency) that
corresponds
to the Numeric Label and is directly below the Matched Rank in the 1st
row.

Sample Data Layout:
Columns: "A" = Numeric Label, "B" = Rank, "C" = Total, "D" = Text Labels,
"E":
"I" = Rank & Frequency (6th-10th Position). The Data starts on row 2 with
Numeric Label 220. Data row 2 to 11.

Label Rank Total POS 6th 7th 8th 9th 10th
220 18 Total Rank 14 10 17 12 18
220 18 1041 Freq 7 6 5 3 3
470 16 Total Rank 6 15 13 17 11
470 16 1058 Freq 4 4 3 3 2
180 14 Total Rank 13 10 16 12 14
180 14 1042 Freq 6 5 4 3 3
400 13 Total Rank 11 14 5 23 13
400 13 1053 Freq 5 5 4 4 3
40 11 Total Rank 9 11 13 15 14
40 11 1040 Freq 6 5 4 4 3


Expected Results:
Label 220 - Row2 Column "I" Rank 18 & Row3 Frequency 3 should have an
outline
Border.
Label 180 - Row6 Column "I" Rank 14 & Row7 Frequency 3 should have an
outline
Border.
Label 400 - Row8 Column "I" Rank 13 & Row9 Frequency 3 should have an
outline
Border.
Label 40 - Row10 Column "F" Rank 11 & Row11 Frequency 5 should have an
outline Border.

Thanks
Sam

--
Message posted via http://www.officekb.com

Do we need to deal with possibly empty cells in columns B and E:I?

Also, based on your sample data, the first two ranks are 18. Will
there/might there be other ranks of 18 in column B?

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:71740450eb94c@uwe...
Hi All,

I would like to use Conditional Formatting (Excel 2003 Windows) to put a
Border around two cells in the same column. The second cell to be
formatted
will always be directly beneath the first cell; i.e. cell I2 (1st) and I3
(2nd).

There are two consecutive rows for each Numeric Label - the Rank is the
1st
row and the Frequency the 2nd row. The Rank is listed twice in column "B"
for
each Numeric Label for sorting purposes.
Each Rank in a row is unique.

The criteria to Conditionally Format the cells - Format Rank and related
Frequency:
1. Match Rank =10 (greater than or equal to 10) in the 1st row of the
relevant Numeric Label - CF is a Border. The Ranks to be matched may be in
any column between "E" and "I".

2. Put a Border around the cell in the 2nd row (Frequency) that
corresponds
to the Numeric Label and is directly below the Matched Rank in the 1st
row.

Sample Data Layout:
Columns: "A" = Numeric Label, "B" = Rank, "C" = Total, "D" = Text Labels,
"E":
"I" = Rank & Frequency (6th-10th Position). The Data starts on row 2 with
Numeric Label 220. Data row 2 to 11.

Label Rank Total POS 6th 7th 8th 9th 10th
220 18 Total Rank 14 10 17 12 18
220 18 1041 Freq 7 6 5 3 3
470 16 Total Rank 6 15 13 17 11
470 16 1058 Freq 4 4 3 3 2
180 14 Total Rank 13 10 16 12 14
180 14 1042 Freq 6 5 4 3 3
400 13 Total Rank 11 14 5 23 13
400 13 1053 Freq 5 5 4 4 3
40 11 Total Rank 9 11 13 15 14
40 11 1040 Freq 6 5 4 4 3


Expected Results:
Label 220 - Row2 Column "I" Rank 18 & Row3 Frequency 3 should have an
outline
Border.
Label 180 - Row6 Column "I" Rank 14 & Row7 Frequency 3 should have an
outline
Border.
Label 400 - Row8 Column "I" Rank 13 & Row9 Frequency 3 should have an
outline
Border.
Label 40 - Row10 Column "F" Rank 11 & Row11 Frequency 5 should have an
outline Border.

Thanks
Sam

--
Message posted via http://www.officekb.com

This seems to work and accounts for empty cells. I'm assuming a rank is not
duplicated.

Select the range E2:I11
Goto FormatConditional Formatting
Formula Is:

=OR(AND(MOD(ROW(),2)=0,COUNTIF($B:$B,$B2)=2,OFFSET (E2,1,,)<"",$B2=E2),AND(E2<"",MOD(ROW(),2)=1,COU NTIF($B:$B,$B2)=2,$B2=OFFSET(E2,-1,,)))

Click the Format button
Apply the border of your choice
OK out

Biff

"T. Valko" wrote in message
...
Do we need to deal with possibly empty cells in columns B and E:I?

Also, based on your sample data, the first two ranks are 18. Will
there/might there be other ranks of 18 in column B?

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:71740450eb94c@uwe...
Hi All,

I would like to use Conditional Formatting (Excel 2003 Windows) to put a
Border around two cells in the same column. The second cell to be
formatted
will always be directly beneath the first cell; i.e. cell I2 (1st) and I3
(2nd).

There are two consecutive rows for each Numeric Label - the Rank is the
1st
row and the Frequency the 2nd row. The Rank is listed twice in column "B"
for
each Numeric Label for sorting purposes.
Each Rank in a row is unique.

The criteria to Conditionally Format the cells - Format Rank and related
Frequency:
1. Match Rank =10 (greater than or equal to 10) in the 1st row of the
relevant Numeric Label - CF is a Border. The Ranks to be matched may be
in
any column between "E" and "I".

2. Put a Border around the cell in the 2nd row (Frequency) that
corresponds
to the Numeric Label and is directly below the Matched Rank in the 1st
row.

Sample Data Layout:
Columns: "A" = Numeric Label, "B" = Rank, "C" = Total, "D" = Text Labels,
"E":
"I" = Rank & Frequency (6th-10th Position). The Data starts on row 2 with
Numeric Label 220. Data row 2 to 11.

Label Rank Total POS 6th 7th 8th 9th 10th
220 18 Total Rank 14 10 17 12 18
220 18 1041 Freq 7 6 5 3 3
470 16 Total Rank 6 15 13 17 11
470 16 1058 Freq 4 4 3 3 2
180 14 Total Rank 13 10 16 12 14
180 14 1042 Freq 6 5 4 3 3
400 13 Total Rank 11 14 5 23 13
400 13 1053 Freq 5 5 4 4 3
40 11 Total Rank 9 11 13 15 14
40 11 1040 Freq 6 5 4 4 3


Expected Results:
Label 220 - Row2 Column "I" Rank 18 & Row3 Frequency 3 should have an
outline
Border.
Label 180 - Row6 Column "I" Rank 14 & Row7 Frequency 3 should have an
outline
Border.
Label 400 - Row8 Column "I" Rank 13 & Row9 Frequency 3 should have an
outline
Border.
Label 40 - Row10 Column "F" Rank 11 & Row11 Frequency 5 should have an
outline Border.

Thanks
Sam

--
Message posted via http://www.officekb.com

Hi Biff,

Thank you for your assistance.

T. Valko wrote:
This seems to work and accounts for empty cells. I'm assuming a rank is not duplicated.

Could your Formula be adapted to take into account duplicate Ranks (other
Numeric Labels having the same Rank)?

Select the range E2:I11
Goto FormatConditional Formatting
Formula Is:

=OR(AND(MOD(ROW(),2)=0,COUNTIF($B:$B,$B2)=2,OFFSE T(E2,1,,)<"",$B2=E2),AND(E2<"",MOD(ROW(),2)=1,CO UNTIF($B:$B,$B2)=2,$B2=OFFSET(E2,-1,,)))

Click the Format button
Apply the border of your choice
OK out

Further assistance very much appreciated.

Cheers,
Sam

--
Message posted via http://www.officekb.com

Ok, if we don't have to be concerned with empty cells we can reduce the
formula to:

=OR(AND(MOD(ROW(),2)=0,$B2=E2),AND(MOD(ROW(),2)=1, $B2=OFFSET(E2,-1,,)))

And it will handle multiple duplicates.

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:717a3f2cd22dc@uwe...
Hi Biff,

Thank you for your assistance.

T. Valko wrote:
This seems to work and accounts for empty cells. I'm assuming a rank is
not duplicated.

Could your Formula be adapted to take into account duplicate Ranks (other
Numeric Labels having the same Rank)?

Select the range E2:I11
Goto FormatConditional Formatting
Formula Is:

=OR(AND(MOD(ROW(),2)=0,COUNTIF($B:$B,$B2)=2,OFFS ET(E2,1,,)<"",$B2=E2),AND(E2<"",MOD(ROW(),2)=1,C OUNTIF($B:$B,$B2)=2,$B2=OFFSET(E2,-1,,)))

Click the Format button
Apply the border of your choice
OK out

Further assistance very much appreciated.

Cheers,
Sam

--
Message posted via http://www.officekb.com

Hi Biff,

Brilliant! Very much appreciated.

Cheers,
Sam

T. Valko wrote:
Ok, if we don't have to be concerned with empty cells we can reduce the
formula to:

=OR(AND(MOD(ROW(),2)=0,$B2=E2),AND(MOD(ROW(),2)=1 ,$B2=OFFSET(E2,-1,,)))

And it will handle multiple duplicates.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200704/1

Hi Biff,

Thank you for your input and assistance.

T. Valko wrote:
Do we need to deal with possibly empty cells in columns B and E:I?

No

Also, based on your sample data, the first two ranks are 18. Will
there/might there be other ranks of 18 in column B?

Yes. Will I need a different Formula to Max's to take care of situations
where there will be other Numeric Labels with the same Rank value?

Please advise

Cheers,
Sam

--
Message posted via http://www.officekb.com