Conditionally format last row of matching criteria
 

I want to create an underline border for a row containing a particular value.

Example:
(Headers) PO# Inv# Amt Status
(Data) ABC 123 $50.12 Multiple
ABC 456 $47.00 Multiple
AXX 789 $21.00

In this example, I want an underline to appear under the last data row that
contains a repeated PO#. So an underline would appear under the middle row,
because that is the last row of the "ABC" PO#. However, I do NOT want
underlines to appear under rows that only have a single PO#.

The status column is set up to read "multiple" if there are multiple rows
using that PO# and the status is blank if it is a single occurrence. The rows
are usually sorted by PO#.




--
KKD

Conditionally format last row of matching criteria
 

I think you want the line only when Status in the current row is 'Multiple'
and the status in next row is blank...

Assuming you have headers and your data starts at row 2... select the range
you want to underline starting at second row and use the
FORMULA IS in Conditional Formatting as
=AND($D2="Multiple",$D3="")
and choose the Underline format

"KKD" wrote:

I want to create an underline border for a row containing a particular value.

Example:
(Headers) PO# Inv# Amt Status
(Data) ABC 123 $50.12 Multiple
ABC 456 $47.00 Multiple
AXX 789 $21.00

In this example, I want an underline to appear under the last data row that
contains a repeated PO#. So an underline would appear under the middle row,
because that is the last row of the "ABC" PO#. However, I do NOT want
underlines to appear under rows that only have a single PO#.

The status column is set up to read "multiple" if there are multiple rows
using that PO# and the status is blank if it is a single occurrence. The rows
are usually sorted by PO#.




--
KKD

Conditionally format last row of matching criteria
 

This conditional formatting formula worked for me (I selected A2:D100)
=AND(COUNTIF($A$2:$A$100,$A2)1,COUNTIF(A2:$A$100, $A2)=1)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"KKD" wrote in message
...
I want to create an underline border for a row containing a particular
value.

Example:
(Headers) PO# Inv# Amt Status
(Data) ABC 123 $50.12 Multiple
ABC 456 $47.00 Multiple
AXX 789 $21.00

In this example, I want an underline to appear under the last data row
that
contains a repeated PO#. So an underline would appear under the middle
row,
because that is the last row of the "ABC" PO#. However, I do NOT want
underlines to appear under rows that only have a single PO#.

The status column is set up to read "multiple" if there are multiple rows
using that PO# and the status is blank if it is a single occurrence. The
rows
are usually sorted by PO#.




--
KKD

Conditionally format last row of matching criteria
 

The problem with that formula is that if there are two PO#s next to each
other they do not get separated.

Example:

Example:
(Headers) PO# Inv# Amt Status
(Data) ABC 123 $50.12 Multiple
ABC 456 $47.00 Multiple
AXX 789 $21.00 Multiple
AXX 987 $15.00 Multiple

With the suggested formula, no line would appear under second line, even
though it is the final row containing "ABC", because the next row is also a
multiple, but it is a new PO#.


--
KKD


"Sheeloo" wrote:

I think you want the line only when Status in the current row is 'Multiple'
and the status in next row is blank...

Assuming you have headers and your data starts at row 2... select the range
you want to underline starting at second row and use the
FORMULA IS in Conditional Formatting as
=AND($D2="Multiple",$D3="")
and choose the Underline format

"KKD" wrote:

I want to create an underline border for a row containing a particular value.

Example:
(Headers) PO# Inv# Amt Status
(Data) ABC 123 $50.12 Multiple
ABC 456 $47.00 Multiple
AXX 789 $21.00

In this example, I want an underline to appear under the last data row that
contains a repeated PO#. So an underline would appear under the middle row,
because that is the last row of the "ABC" PO#. However, I do NOT want
underlines to appear under rows that only have a single PO#.

The status column is set up to read "multiple" if there are multiple rows
using that PO# and the status is blank if it is a single occurrence. The rows
are usually sorted by PO#.




--
KKD

Conditionally format last row of matching criteria
 

Try
=NOT(AND($D2="Multiple",$D3="Multiple"))
"KKD" wrote:

The problem with that formula is that if there are two PO#s next to each
other they do not get separated.

Example:

Example:
(Headers) PO# Inv# Amt Status
(Data) ABC 123 $50.12 Multiple
ABC 456 $47.00 Multiple
AXX 789 $21.00 Multiple
AXX 987 $15.00 Multiple

With the suggested formula, no line would appear under second line, even
though it is the final row containing "ABC", because the next row is also a
multiple, but it is a new PO#.


--
KKD


"Sheeloo" wrote:

I think you want the line only when Status in the current row is 'Multiple'
and the status in next row is blank...

Assuming you have headers and your data starts at row 2... select the range
you want to underline starting at second row and use the
FORMULA IS in Conditional Formatting as
=AND($D2="Multiple",$D3="")
and choose the Underline format

"KKD" wrote:

I want to create an underline border for a row containing a particular value.

Example:
(Headers) PO# Inv# Amt Status
(Data) ABC 123 $50.12 Multiple
ABC 456 $47.00 Multiple
AXX 789 $21.00

In this example, I want an underline to appear under the last data row that
contains a repeated PO#. So an underline would appear under the middle row,
because that is the last row of the "ABC" PO#. However, I do NOT want
underlines to appear under rows that only have a single PO#.

The status column is set up to read "multiple" if there are multiple rows
using that PO# and the status is blank if it is a single occurrence. The rows
are usually sorted by PO#.




--
KKD

Conditionally format last row of matching criteria
 

insert the following conditional formatting formula for A2
=COUNTIF($A$2:A2,A2)=COUNTIF($A$2:$A$100,A2)

then copy-pastespecial-Formats

HIH



On 18 Mar, 19:08, KKD wrote:
The problem with that formula is that if there are two PO#s next to each
other they do not get separated.

Example:

Example:
Â*(Headers) Â*PO# Â* Â*Inv# Â* Â*Amt Â* Â* Â* Status
Â*(Data) Â* Â* Â*ABC Â* Â* 123 Â* Â* Â*$50.12 Â* Multiple
Â* Â* Â* Â* Â* Â* Â* Â* ABC Â* Â* 456 Â* Â* Â*$47.00 Â* Multiple
Â* Â* Â* Â* Â* Â* Â* Â* AXX Â* Â* 789 Â* Â* Â*$21.00 Â* Multiple
Â* Â* Â* Â* Â* Â* Â* Â* AXX Â* Â* 987 Â* Â* Â*$15.00 Â* Multiple

With the suggested formula, no line would appear under second line, even
though it is the final row containing "ABC", because the next row is also a
multiple, but it is a new PO#.

--
KKD



"Sheeloo" wrote:
I think you want the line only when Status in the current row is 'Multiple'
and the status in next row is blank...

Assuming you have headers and your data starts at row 2... select the range
you want to underline starting at second row and use the
FORMULA IS in Conditional Formatting as
=AND($D2="Multiple",$D3="")
and choose the Underline format

"KKD" wrote:

I want to create an underline border for a row containing a particular value.

Example:
(Headers) Â*PO# Â* Â*Inv# Â* Â*Amt Â* Â* Â* Status
(Data) Â* Â* Â*ABC Â* Â* 123 Â* Â* Â*$50.12 Â* Multiple
Â* Â* Â* Â* Â* Â* Â* Â*ABC Â* Â* 456 Â* Â* Â*$47.00 Â* Multiple
Â* Â* Â* Â* Â* Â* Â* Â*AXX Â* Â* 789 Â* Â* Â*$21.00 Â*

In this example, I want an underline to appear under the last data row that
contains a repeated PO#. So an underline would appear under the middle row,
because that is the last row of the "ABC" PO#. However, I do NOT want
underlines to appear under rows that only have a single PO#.

The status column is set up to read "multiple" if there are multiple rows
using that PO# and the status is blank if it is a single occurrence. The rows
are usually sorted by PO#.

--
KKD- Ukryj cytowany tekst -

- Pokaż cytowany tekst -