Comparing Data in Excel 2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Thank you Ken, this formula gave me the results that I wanted; I just changed
">" to "=". -- Carolyn
 
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
 
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
 
Congratulations!

Biff

Teethless mama said:
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
Click to expand...
 
Back
Top