| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Taking out unsubscribed emails by comparing 2 Excel files (Excel 2003) | Excel Discussion (Misc queries) | |||
| comparing excel 2003 and excel 2007 | Excel Discussion (Misc queries) | |||
| Excel data comparing | Excel Worksheet Functions | |||
| excel 2003 help comparing prices for lowest | Excel Worksheet Functions | |||
| Comparing a value to a list of values does not work in Excel 2003. | Excel Worksheet Functions | |||
Hybrid Mode
