Below is a sample list; I need to extract or highlight names in Column A that
have a zero value in column B, but if the name in Column A is listed multiple
times and the value in column B is greater than zero, it should not be
extracted or highlighted. So for the list below the result should be User 4
and User 5. Thank you.

Column A Column B
Name PST Folder Size
User 1 0
User 2 15
User 3 18
User 1 22
User 4 0
User 5 0

select column A
<format><conditional format>
change value is to formula is
=countif(A:A,A1,B:B)=0
set format patterns to what you want
occasionally I have to go back into conditional formatting and remove quote
marks I do not want or change absolute and relative cell referencing as
needed. i have never been able to truely identify why, but think I am
probalbly just sloppy when I first do it.

"Diamonds_Mine" wrote:

> Below is a sample list; I need to extract or highlight names in Column A that
> have a zero value in column B, but if the name in Column A is listed multiple
> times and the value in column B is greater than zero, it should not be
> extracted or highlighted. So for the list below the result should be User 4
> and User 5. Thank you.
>
> Column A Column B
> Name PST Folder Size
> User 1 0
> User 2 15
> User 3 18
> User 1 22
> User 4 0
> User 5 0
>

You can highlight with conditional formatting. With your data in cells
A2:B7, it would be

=AND(COUNTIF($A$2:$A$7,$A2)=1,$B2>0)

with the conditional format, whatever you like to highlight things.

For extraction, you can put the above formula in an adjacent column,
and use Advance Filtering. The formula above should evaluate to True
for rows meeting your conditions, otherwise False, as I understand
your problem.

Good luck.

Ken
Norfolk, Va


On Jun 21, 8:10 am, Diamonds_Mine
<DiamondsM...@discussions.microsoft.com> wrote:
> Below is a sample list; I need to extract or highlight names in Column A that
> have a zero value in column B, but if the name in Column A is listed multiple
> times and the value in column B is greater than zero, it should not be
> extracted or highlighted. So for the list below the result should be User 4
> and User 5. Thank you.
>
> Column A Column B
> Name PST Folder Size
> User 1 0
> User 2 15
> User 3 18
> User 1 22
> User 4 0
> User 5 0

Thank you Ken, this formula gave me the results that I wanted; I just changed
">" to "=". -- Carolyn

"(E-Mail Removed)" wrote:

> You can highlight with conditional formatting. With your data in cells
> A2:B7, it would be
>
> =AND(COUNTIF($A$2:$A$7,$A2)=1,$B2>0)
>
> with the conditional format, whatever you like to highlight things.
>
> For extraction, you can put the above formula in an adjacent column,
> and use Advance Filtering. The formula above should evaluate to True
> for rows meeting your conditions, otherwise False, as I understand
> your problem.
>
> Good luck.
>
> Ken
> Norfolk, Va
>
>
> On Jun 21, 8:10 am, Diamonds_Mine
> <DiamondsM...@discussions.microsoft.com> wrote:
> > Below is a sample list; I need to extract or highlight names in Column A that
> > have a zero value in column B, but if the name in Column A is listed multiple
> > times and the value in column B is greater than zero, it should not be
> > extracted or highlighted. So for the list below the result should be User 4
> > and User 5. Thank you.
> >
> > Column A Column B
> > Name PST Folder Size
> > User 1 0
> > User 2 15
> > User 3 18
> > User 1 22
> > User 4 0
> > User 5 0
>
>
>

Try Auto Filter or Advanced Filter are the best way to do. If you preferred
the formula then
try this:
"Name" is a define name range
"PSTFS" is a define name range

=IF(ISERR(SMALL(IF(PSTFS=0,ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1))),"",INDEX(Name,SMALL(IF(PSTFS=0,ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down

or this:
=IF(COUNTIF(PSTFS,0)>=ROWS($1:1),INDEX(Name,SMALL(IF(PSTFS=0,ROW(Name)-MIN(ROW(Name))+1,ROWS($1:1)),ROWS($1:1))),"")

ctrl+shift+enter, not just enter
copy down

"Diamonds_Mine" wrote:

> Below is a sample list; I need to extract or highlight names in Column A that
> have a zero value in column B, but if the name in Column A is listed multiple
> times and the value in column B is greater than zero, it should not be
> extracted or highlighted. So for the list below the result should be User 4
> and User 5. Thank you.
>
> Column A Column B
> Name PST Folder Size
> User 1 0
> User 2 15
> User 3 18
> User 1 22
> User 4 0
> User 5 0
>

Correction for second formula:

=IF(COUNTIF(PSTFS,0)>=ROWS($1:1),INDEX(Name,SMALL(IF(PSTFS=0,ROW(Name)-MIN(ROW(Name))+1),ROWS($1:1))),"")

ctrl+shift+enter, not just enter
copy down

"Teethless mama" wrote:

> Try Auto Filter or Advanced Filter are the best way to do. If you preferred
> the formula then
> try this:
> "Name" is a define name range
> "PSTFS" is a define name range
>
> =IF(ISERR(SMALL(IF(PSTFS=0,ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1))),"",INDEX(Name,SMALL(IF(PSTFS=0,ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1))))
>
> ctrl+shift+enter, not just enter
> copy down
>
> or this:
> =IF(COUNTIF(PSTFS,0)>=ROWS($1:1),INDEX(Name,SMALL(IF(PSTFS=0,ROW(Name)-MIN(ROW(Name))+1,ROWS($1:1)),ROWS($1:1))),"")
>
> ctrl+shift+enter, not just enter
> copy down
>
> "Diamonds_Mine" wrote:
>
> > Below is a sample list; I need to extract or highlight names in Column A that
> > have a zero value in column B, but if the name in Column A is listed multiple
> > times and the value in column B is greater than zero, it should not be
> > extracted or highlighted. So for the list below the result should be User 4
> > and User 5. Thank you.
> >
> > Column A Column B
> > Name PST Folder Size
> > User 1 0
> > User 2 15
> > User 3 18
> > User 1 22
> > User 4 0
> > User 5 0
> >

note that should have been sumif
=sumtif(A:A,A1,B:B)=0
"bj" wrote:

> select column A
> <format><conditional format>
> change value is to formula is
> =countif(A:A,A1,B:B)=0
> set format patterns to what you want
> occasionally I have to go back into conditional formatting and remove quote
> marks I do not want or change absolute and relative cell referencing as
> needed. i have never been able to truely identify why, but think I am
> probalbly just sloppy when I first do it.
>
> "Diamonds_Mine" wrote:
>
> > Below is a sample list; I need to extract or highlight names in Column A that
> > have a zero value in column B, but if the name in Column A is listed multiple
> > times and the value in column B is greater than zero, it should not be
> > extracted or highlighted. So for the list below the result should be User 4
> > and User 5. Thank you.
> >
> > Column A Column B
> > Name PST Folder Size
> > User 1 0
> > User 2 15
> > User 3 18
> > User 1 22
> > User 4 0
> > User 5 0
> >

Congratulations!

Biff

"Teethless mama" <(E-Mail Removed)> wrote in message
news:3810B988-6F26-4E08-9268-(E-Mail Removed)...
> Correction for second formula:
>
> =IF(COUNTIF(PSTFS,0)>=ROWS($1:1),INDEX(Name,SMALL(IF(PSTFS=0,ROW(Name)-MIN(ROW(Name))+1),ROWS($1:1))),"")
>
> ctrl+shift+enter, not just enter
> copy down
>
> "Teethless mama" wrote:
>
>> Try Auto Filter or Advanced Filter are the best way to do. If you
>> preferred
>> the formula then
>> try this:
>> "Name" is a define name range
>> "PSTFS" is a define name range
>>
>> =IF(ISERR(SMALL(IF(PSTFS=0,ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1))),"",INDEX(Name,SMALL(IF(PSTFS=0,ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1))))
>>
>> ctrl+shift+enter, not just enter
>> copy down
>>
>> or this:
>> =IF(COUNTIF(PSTFS,0)>=ROWS($1:1),INDEX(Name,SMALL(IF(PSTFS=0,ROW(Name)-MIN(ROW(Name))+1,ROWS($1:1)),ROWS($1:1))),"")
>>
>> ctrl+shift+enter, not just enter
>> copy down
>>
>> "Diamonds_Mine" wrote:
>>
>> > Below is a sample list; I need to extract or highlight names in Column
>> > A that
>> > have a zero value in column B, but if the name in Column A is listed
>> > multiple
>> > times and the value in column B is greater than zero, it should not be
>> > extracted or highlighted. So for the list below the result should be
>> > User 4
>> > and User 5. Thank you.
>> >
>> > Column A Column B
>> > Name PST Folder Size
>> > User 1 0
>> > User 2 15
>> > User 3 18
>> > User 1 22
>> > User 4 0
>> > User 5 0
>> >