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?

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?

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?

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

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

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?

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?

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?

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?

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?

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?

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?

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?

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?

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?