Comparing Data in Excel 2003
 

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

Comparing Data in Excel 2003
 

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

Comparing Data in Excel 2003
 

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

=AND(COUNTIF($A$2:$A$7,$A2)=1,$B20)

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
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

Comparing Data in Excel 2003
 

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

" 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,$B20)

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
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

Comparing Data in Excel 2003
 

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

Comparing Data in Excel 2003
 

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

Comparing Data in Excel 2003
 

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

Comparing Data in Excel 2003
 

Congratulations!

Biff

"Teethless mama" wrote in message
...
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