| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract list of units based on error criteria to new list
I have a list that looks like this:
Column A Column B 02941842 34,939.55 02941840 34,939.55 03062084 #N/A 02657582 30,203.20 03126311 #N/A 03105157 28,430.54 I need a seperate summary list that extracts the items from Column A with an error in Column B. Can excel do this? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract list of units based on error criteria to new list
In D1:
=IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))),"",INDEX(rangeA,SM ALL(IF(ISERROR(rangeB),ROW(INDIRECT("1:"&ROWS(rang eA)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down In E1: =IF(D1="","",VLOOKUP(D1,ranges,2,0)) copy down "Sheila" wrote: I have a list that looks like this: Column A Column B 02941842 34,939.55 02941840 34,939.55 03062084 #N/A 02657582 30,203.20 03126311 #N/A 03105157 28,430.54 I need a seperate summary list that extracts the items from Column A with an error in Column B. Can excel do this? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract list of units based on error criteria to new list
In D1:
=IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))),"",INDEX(rangeA,SM ALL(IF(ISERROR(rangeB),ROW(INDIRECT("1:"&ROWS(rang eA)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down In E1: =IF(D1="","",VLOOKUP(D1,ranges,2,0)) copy down "Sheila" wrote: I have a list that looks like this: Column A Column B 02941842 34,939.55 02941840 34,939.55 03062084 #N/A 02657582 30,203.20 03126311 #N/A 03105157 28,430.54 I need a seperate summary list that extracts the items from Column A with an error in Column B. Can excel do this? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract list of units based on error criteria to new list
Apply data|filter|autofilter to column B.
Filter to show the #n/a's. Select those visible rows edit|copy then off to the new location edit|paste Sheila wrote: I have a list that looks like this: Column A Column B 02941842 34,939.55 02941840 34,939.55 03062084 #N/A 02657582 30,203.20 03126311 #N/A 03105157 28,430.54 I need a seperate summary list that extracts the items from Column A with an error in Column B. Can excel do this? -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract list of units based on error criteria to new list
Apply data|filter|autofilter to column B.
Filter to show the #n/a's. Select those visible rows edit|copy then off to the new location edit|paste Sheila wrote: I have a list that looks like this: Column A Column B 02941842 34,939.55 02941840 34,939.55 03062084 #N/A 02657582 30,203.20 03126311 #N/A 03105157 28,430.54 I need a seperate summary list that extracts the items from Column A with an error in Column B. Can excel do this? -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract list of units based on error criteria to new list
Since they wanted to extract based on #N/A you should probably use:
(SMALL(IF(ISNA(rangeB) Your error trap is horrendous! <bg =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))) It can be replaced with: =IF(ROWS($1:1)<=COUNTIF(rangeB,#N/A) I give up trying to convince you that this is not the best way to go: ROW(INDIRECT("1:"&ROWS(rangeA))) -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... In D1: =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))),"",INDEX(rangeA,SM ALL(IF(ISERROR(rangeB),ROW(INDIRECT("1:"&ROWS(rang eA)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down In E1: =IF(D1="","",VLOOKUP(D1,ranges,2,0)) copy down "Sheila" wrote: I have a list that looks like this: Column A Column B 02941842 34,939.55 02941840 34,939.55 03062084 #N/A 02657582 30,203.20 03126311 #N/A 03105157 28,430.54 I need a seperate summary list that extracts the items from Column A with an error in Column B. Can excel do this? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract list of units based on error criteria to new list
Since they wanted to extract based on #N/A you should probably use:
(SMALL(IF(ISNA(rangeB) Your error trap is horrendous! <bg =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))) It can be replaced with: =IF(ROWS($1:1)<=COUNTIF(rangeB,#N/A) I give up trying to convince you that this is not the best way to go: ROW(INDIRECT("1:"&ROWS(rangeA))) -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... In D1: =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))),"",INDEX(rangeA,SM ALL(IF(ISERROR(rangeB),ROW(INDIRECT("1:"&ROWS(rang eA)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down In E1: =IF(D1="","",VLOOKUP(D1,ranges,2,0)) copy down "Sheila" wrote: I have a list that looks like this: Column A Column B 02941842 34,939.55 02941840 34,939.55 03062084 #N/A 02657582 30,203.20 03126311 #N/A 03105157 28,430.54 I need a seperate summary list that extracts the items from Column A with an error in Column B. Can excel do this? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract list of units based on error criteria to new list
I need a seperate summary list that extracts the items from Column A with
an error in Column B. Since they wanted to extract based on #N/A you should probably use: (SMALL(IF(ISNA(rangeB) Or maybe not. For some reason I read it as extract based on #N/A but apparently not. So ISERROR would be appropriate. But the error trap is still horrendous no matter how you slice it! <bg So, for all errors: =IF(ROWS($1:1)<=SUMPRODUCT(--(ISERROR(rangeB))) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Since they wanted to extract based on #N/A you should probably use: (SMALL(IF(ISNA(rangeB) Your error trap is horrendous! <bg =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))) It can be replaced with: =IF(ROWS($1:1)<=COUNTIF(rangeB,#N/A) I give up trying to convince you that this is not the best way to go: ROW(INDIRECT("1:"&ROWS(rangeA))) -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... In D1: =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))),"",INDEX(rangeA,SM ALL(IF(ISERROR(rangeB),ROW(INDIRECT("1:"&ROWS(rang eA)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down In E1: =IF(D1="","",VLOOKUP(D1,ranges,2,0)) copy down "Sheila" wrote: I have a list that looks like this: Column A Column B 02941842 34,939.55 02941840 34,939.55 03062084 #N/A 02657582 30,203.20 03126311 #N/A 03105157 28,430.54 I need a seperate summary list that extracts the items from Column A with an error in Column B. Can excel do this? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract list of units based on error criteria to new list
I need a seperate summary list that extracts the items from Column A with
an error in Column B. Since they wanted to extract based on #N/A you should probably use: (SMALL(IF(ISNA(rangeB) Or maybe not. For some reason I read it as extract based on #N/A but apparently not. So ISERROR would be appropriate. But the error trap is still horrendous no matter how you slice it! <bg So, for all errors: =IF(ROWS($1:1)<=SUMPRODUCT(--(ISERROR(rangeB))) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Since they wanted to extract based on #N/A you should probably use: (SMALL(IF(ISNA(rangeB) Your error trap is horrendous! <bg =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))) It can be replaced with: =IF(ROWS($1:1)<=COUNTIF(rangeB,#N/A) I give up trying to convince you that this is not the best way to go: ROW(INDIRECT("1:"&ROWS(rangeA))) -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... In D1: =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))),"",INDEX(rangeA,SM ALL(IF(ISERROR(rangeB),ROW(INDIRECT("1:"&ROWS(rang eA)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down In E1: =IF(D1="","",VLOOKUP(D1,ranges,2,0)) copy down "Sheila" wrote: I have a list that looks like this: Column A Column B 02941842 34,939.55 02941840 34,939.55 03062084 #N/A 02657582 30,203.20 03126311 #N/A 03105157 28,430.54 I need a seperate summary list that extracts the items from Column A with an error in Column B. Can excel do this? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract list of units based on error criteria to new list
Array entered:
=IF(ROWS($1:1)<=SUMPRODUCT(--ISERROR(rngB)),INDEX(rngA,SMALL(IF(ISERROR(rngB),R OW(rngA)-MIN(ROW(rngA))+1),ROWS($1:1))),"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... I need a seperate summary list that extracts the items from Column A with an error in Column B. Since they wanted to extract based on #N/A you should probably use: (SMALL(IF(ISNA(rangeB) Or maybe not. For some reason I read it as extract based on #N/A but apparently not. So ISERROR would be appropriate. But the error trap is still horrendous no matter how you slice it! <bg So, for all errors: =IF(ROWS($1:1)<=SUMPRODUCT(--(ISERROR(rangeB))) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Since they wanted to extract based on #N/A you should probably use: (SMALL(IF(ISNA(rangeB) Your error trap is horrendous! <bg =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))) It can be replaced with: =IF(ROWS($1:1)<=COUNTIF(rangeB,#N/A) I give up trying to convince you that this is not the best way to go: ROW(INDIRECT("1:"&ROWS(rangeA))) -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... In D1: =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))),"",INDEX(rangeA,SM ALL(IF(ISERROR(rangeB),ROW(INDIRECT("1:"&ROWS(rang eA)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down In E1: =IF(D1="","",VLOOKUP(D1,ranges,2,0)) copy down "Sheila" wrote: I have a list that looks like this: Column A Column B 02941842 34,939.55 02941840 34,939.55 03062084 #N/A 02657582 30,203.20 03126311 #N/A 03105157 28,430.54 I need a seperate summary list that extracts the items from Column A with an error in Column B. Can excel do this? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract list of units based on error criteria to new list
Array entered:
=IF(ROWS($1:1)<=SUMPRODUCT(--ISERROR(rngB)),INDEX(rngA,SMALL(IF(ISERROR(rngB),R OW(rngA)-MIN(ROW(rngA))+1),ROWS($1:1))),"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... I need a seperate summary list that extracts the items from Column A with an error in Column B. Since they wanted to extract based on #N/A you should probably use: (SMALL(IF(ISNA(rangeB) Or maybe not. For some reason I read it as extract based on #N/A but apparently not. So ISERROR would be appropriate. But the error trap is still horrendous no matter how you slice it! <bg So, for all errors: =IF(ROWS($1:1)<=SUMPRODUCT(--(ISERROR(rangeB))) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Since they wanted to extract based on #N/A you should probably use: (SMALL(IF(ISNA(rangeB) Your error trap is horrendous! <bg =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))) It can be replaced with: =IF(ROWS($1:1)<=COUNTIF(rangeB,#N/A) I give up trying to convince you that this is not the best way to go: ROW(INDIRECT("1:"&ROWS(rangeA))) -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... In D1: =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))),"",INDEX(rangeA,SM ALL(IF(ISERROR(rangeB),ROW(INDIRECT("1:"&ROWS(rang eA)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down In E1: =IF(D1="","",VLOOKUP(D1,ranges,2,0)) copy down "Sheila" wrote: I have a list that looks like this: Column A Column B 02941842 34,939.55 02941840 34,939.55 03062084 #N/A 02657582 30,203.20 03126311 #N/A 03105157 28,430.54 I need a seperate summary list that extracts the items from Column A with an error in Column B. Can excel do this? |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract list of units based on error criteria to new list
Stop whinning like a baby? My formula is show on most Excel text books. Read
"Excel 2003 Bible by John Walkenbach" it show a lot of those formulas. "T. Valko" wrote: Since they wanted to extract based on #N/A you should probably use: (SMALL(IF(ISNA(rangeB) Your error trap is horrendous! <bg =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))) It can be replaced with: =IF(ROWS($1:1)<=COUNTIF(rangeB,#N/A) I give up trying to convince you that this is not the best way to go: ROW(INDIRECT("1:"&ROWS(rangeA))) -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... In D1: =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))),"",INDEX(rangeA,SM ALL(IF(ISERROR(rangeB),ROW(INDIRECT("1:"&ROWS(rang eA)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down In E1: =IF(D1="","",VLOOKUP(D1,ranges,2,0)) copy down "Sheila" wrote: I have a list that looks like this: Column A Column B 02941842 34,939.55 02941840 34,939.55 03062084 #N/A 02657582 30,203.20 03126311 #N/A 03105157 28,430.54 I need a seperate summary list that extracts the items from Column A with an error in Column B. Can excel do this? |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract list of units based on error criteria to new list
Stop whinning like a baby? My formula is show on most Excel text books. Read
"Excel 2003 Bible by John Walkenbach" it show a lot of those formulas. "T. Valko" wrote: Since they wanted to extract based on #N/A you should probably use: (SMALL(IF(ISNA(rangeB) Your error trap is horrendous! <bg =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))) It can be replaced with: =IF(ROWS($1:1)<=COUNTIF(rangeB,#N/A) I give up trying to convince you that this is not the best way to go: ROW(INDIRECT("1:"&ROWS(rangeA))) -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... In D1: =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))),"",INDEX(rangeA,SM ALL(IF(ISERROR(rangeB),ROW(INDIRECT("1:"&ROWS(rang eA)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down In E1: =IF(D1="","",VLOOKUP(D1,ranges,2,0)) copy down "Sheila" wrote: I have a list that looks like this: Column A Column B 02941842 34,939.55 02941840 34,939.55 03062084 #N/A 02657582 30,203.20 03126311 #N/A 03105157 28,430.54 I need a seperate summary list that extracts the items from Column A with an error in Column B. Can excel do this? |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract list of units based on error criteria to new list
Stop whinning like a baby?
I'm trying to show you a better way to do things. If you can't appreciate that then....you're welcome and thanks for the feedback! -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... Stop whinning like a baby? My formula is show on most Excel text books. Read "Excel 2003 Bible by John Walkenbach" it show a lot of those formulas. "T. Valko" wrote: Since they wanted to extract based on #N/A you should probably use: (SMALL(IF(ISNA(rangeB) Your error trap is horrendous! <bg =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))) It can be replaced with: =IF(ROWS($1:1)<=COUNTIF(rangeB,#N/A) I give up trying to convince you that this is not the best way to go: ROW(INDIRECT("1:"&ROWS(rangeA))) -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... In D1: =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))),"",INDEX(rangeA,SM ALL(IF(ISERROR(rangeB),ROW(INDIRECT("1:"&ROWS(rang eA)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down In E1: =IF(D1="","",VLOOKUP(D1,ranges,2,0)) copy down "Sheila" wrote: I have a list that looks like this: Column A Column B 02941842 34,939.55 02941840 34,939.55 03062084 #N/A 02657582 30,203.20 03126311 #N/A 03105157 28,430.54 I need a seperate summary list that extracts the items from Column A with an error in Column B. Can excel do this? |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extract list of units based on error criteria to new list
Stop whinning like a baby?
I'm trying to show you a better way to do things. If you can't appreciate that then....you're welcome and thanks for the feedback! -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... Stop whinning like a baby? My formula is show on most Excel text books. Read "Excel 2003 Bible by John Walkenbach" it show a lot of those formulas. "T. Valko" wrote: Since they wanted to extract based on #N/A you should probably use: (SMALL(IF(ISNA(rangeB) Your error trap is horrendous! <bg =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))) It can be replaced with: =IF(ROWS($1:1)<=COUNTIF(rangeB,#N/A) I give up trying to convince you that this is not the best way to go: ROW(INDIRECT("1:"&ROWS(rangeA))) -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... In D1: =IF(ISERR(SMALL(IF(ISERROR(rangeB),ROW(INDIRECT("1 :"&ROWS(rangeA)))),ROWS($1:1))),"",INDEX(rangeA,SM ALL(IF(ISERROR(rangeB),ROW(INDIRECT("1:"&ROWS(rang eA)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down In E1: =IF(D1="","",VLOOKUP(D1,ranges,2,0)) copy down "Sheila" wrote: I have a list that looks like this: Column A Column B 02941842 34,939.55 02941840 34,939.55 03062084 #N/A 02657582 30,203.20 03126311 #N/A 03105157 28,430.54 I need a seperate summary list that extracts the items from Column A with an error in Column B. Can excel do this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Criteria Based List | Excel Discussion (Misc queries) | |||
| importing and updating list based on criteria | Excel Discussion (Misc queries) | |||
| Return value from list based on criteria | Excel Worksheet Functions | |||
| add to a cell on a list based on two criteria | New Users to Excel | |||
| Extract multiple records matching criteria from list | Excel Worksheet Functions | |||
Linear Mode
