I've always avoided advanced filtering, but it looks like it's one of the few
ways for me to solve what I need.

I have to take a list of 11K entries, and find (UNIQUELY) the values in
column B, which are associated with the #N/A values in column A. In other
words, I need to basically show all N/A values, and then out of that subset,
show the B values once each.

Can you tell me the basic setup? I know that it takes aligning criteria
above the search columns or something like that from the help, but as
advanced as I am with Excel, I've just never made it to advanced filters, and
don't know how to use them.

Thx.
--
Boris

Assume the header in A is called Header1, in H2 put Header1 and in H2 put
#N/A, select Range A and B and as criteria use $H$1:$H$2, preferably copy to
another location and select unique records only

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"BorisS" wrote in message
...
I've always avoided advanced filtering, but it looks like it's one of the
few
ways for me to solve what I need.

I have to take a list of 11K entries, and find (UNIQUELY) the values in
column B, which are associated with the #N/A values in column A. In other
words, I need to basically show all N/A values, and then out of that
subset,
show the B values once each.

Can you tell me the basic setup? I know that it takes aligning criteria
above the search columns or something like that from the help, but as
advanced as I am with Excel, I've just never made it to advanced filters,
and
don't know how to use them.

Thx.
--
Boris

Not exactly clear on what you meant. You said put Header1 AND #N/A in H2.
Not sure how to put both in the same cell.

Basically, I have 10 columns. Two of them are named Dept. and Account. I
have made a set of spacer rows on top, as I think the advanced filter
instructions said. The top line now reads all the column headings, then I
have three blank rows, and then the headers again, followed by data. Assume
column A is Dept and Column B is Account. What exactly should I do? I
understand that under the first row (the criteria headers) I am supposed to
put '="=#N/A"' (typing all that is between the single quotes), since
otherwise Excel will read it as '=#N/A', which would show #N/A in the
criteria cell, ignoring ther operator. Past this, I am not clear on the
instructions, and that's what I'm looking for help with. So if that
clarifies my example, let me know if you can fill me in on how to structure
this. Thanks.
--
Boris


"Peo Sjoblom" wrote:

Assume the header in A is called Header1, in H2 put Header1 and in H2 put
#N/A, select Range A and B and as criteria use $H$1:$H$2, preferably copy to
another location and select unique records only

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"BorisS" wrote in message
...
I've always avoided advanced filtering, but it looks like it's one of the
few
ways for me to solve what I need.

I have to take a list of 11K entries, and find (UNIQUELY) the values in
column B, which are associated with the #N/A values in column A. In other
words, I need to basically show all N/A values, and then out of that
subset,
show the B values once each.

Can you tell me the basic setup? I know that it takes aligning criteria
above the search columns or something like that from the help, but as
advanced as I am with Excel, I've just never made it to advanced filters,
and
don't know how to use them.

Thx.
--
Boris

Sorry, meant put the header in H1 and #N/A in H2

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"BorisS" wrote in message
...
Not exactly clear on what you meant. You said put Header1 AND #N/A in H2.
Not sure how to put both in the same cell.

Basically, I have 10 columns. Two of them are named Dept. and Account. I
have made a set of spacer rows on top, as I think the advanced filter
instructions said. The top line now reads all the column headings, then I
have three blank rows, and then the headers again, followed by data.
Assume
column A is Dept and Column B is Account. What exactly should I do? I
understand that under the first row (the criteria headers) I am supposed
to
put '="=#N/A"' (typing all that is between the single quotes), since
otherwise Excel will read it as '=#N/A', which would show #N/A in the
criteria cell, ignoring ther operator. Past this, I am not clear on the
instructions, and that's what I'm looking for help with. So if that
clarifies my example, let me know if you can fill me in on how to
structure
this. Thanks.
--
Boris


"Peo Sjoblom" wrote:

Assume the header in A is called Header1, in H2 put Header1 and in H2 put
#N/A, select Range A and B and as criteria use $H$1:$H$2, preferably copy
to
another location and select unique records only

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"BorisS" wrote in message
...
I've always avoided advanced filtering, but it looks like it's one of
the
few
ways for me to solve what I need.

I have to take a list of 11K entries, and find (UNIQUELY) the values in
column B, which are associated with the #N/A values in column A. In
other
words, I need to basically show all N/A values, and then out of that
subset,
show the B values once each.

Can you tell me the basic setup? I know that it takes aligning
criteria
above the search columns or something like that from the help, but as
advanced as I am with Excel, I've just never made it to advanced
filters,
and
don't know how to use them.

Thx.
--
Boris

I had tried what you said, and it didn't produce any results. Not sure
what's wrong.
--
Boris


"Peo Sjoblom" wrote:

Sorry, meant put the header in H1 and #N/A in H2

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"BorisS" wrote in message
...
Not exactly clear on what you meant. You said put Header1 AND #N/A in H2.
Not sure how to put both in the same cell.

Basically, I have 10 columns. Two of them are named Dept. and Account. I
have made a set of spacer rows on top, as I think the advanced filter
instructions said. The top line now reads all the column headings, then I
have three blank rows, and then the headers again, followed by data.
Assume
column A is Dept and Column B is Account. What exactly should I do? I
understand that under the first row (the criteria headers) I am supposed
to
put '="=#N/A"' (typing all that is between the single quotes), since
otherwise Excel will read it as '=#N/A', which would show #N/A in the
criteria cell, ignoring ther operator. Past this, I am not clear on the
instructions, and that's what I'm looking for help with. So if that
clarifies my example, let me know if you can fill me in on how to
structure
this. Thanks.
--
Boris


"Peo Sjoblom" wrote:

Assume the header in A is called Header1, in H2 put Header1 and in H2 put
#N/A, select Range A and B and as criteria use $H$1:$H$2, preferably copy
to
another location and select unique records only

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"BorisS" wrote in message
...
I've always avoided advanced filtering, but it looks like it's one of
the
few
ways for me to solve what I need.

I have to take a list of 11K entries, and find (UNIQUELY) the values in
column B, which are associated with the #N/A values in column A. In
other
words, I need to basically show all N/A values, and then out of that
subset,
show the B values once each.

Can you tell me the basic setup? I know that it takes aligning
criteria
above the search columns or something like that from the help, but as
advanced as I am with Excel, I've just never made it to advanced
filters,
and
don't know how to use them.

Thx.
--
Boris

BorisS wrote:

I had tried what you said, and it didn't produce any results. Not sure
what's wrong.

Maybe I didn't understand what you want to do, if you have 2 xolumns
with data and you have #N/A errors in A and want to display those and
the values in B then my method works


Regards,

Peo Sjoblom

I'll try to clarify what I'd written.

I actually have 10 columns. First row has the headers (call them HeaderA1,
HeaderB1, etc.). Rows 2 and 3 are blank, waiting for the advanced filter
criteria (if I understand correctly how it's supposed to be set up). Row 4
has the headers again (HeaderA4, HeaderB4). All 11K records are below. I
need to have a subset list where I search only the rows that have #N/A values
in the A column, and then return unique records of the resulting B column
values. And I need to have the entire line of 10 column shown in the subset.

So with that prep, can you tell me how I'd set up the advanced filter
dialogue?

Thx.
--
Boris


"Peo Sjoblom" wrote:

BorisS wrote:

I had tried what you said, and it didn't produce any results. Not sure
what's wrong.

Maybe I didn't understand what you want to do, if you have 2 xolumns
with data and you have #N/A errors in A and want to display those and
the values in B then my method works


Regards,

Peo Sjoblom

To filter for the first record for each value in column B, where the
value in column A is #N/A --

In cell A1, enter the heading from cell A4
In cell A2, enter #N/A
Leave cell B2 blank
In cell B2, enter:
=SUMPRODUCT(--($B$4:$B5=B5),--(ISNA($A$4:$A5)))=1

Run the Advanced Filter, using cells A1:B2 as the criteria range.

BorisS wrote:
I'll try to clarify what I'd written.

I actually have 10 columns. First row has the headers (call them HeaderA1,
HeaderB1, etc.). Rows 2 and 3 are blank, waiting for the advanced filter
criteria (if I understand correctly how it's supposed to be set up). Row 4
has the headers again (HeaderA4, HeaderB4). All 11K records are below. I
need to have a subset list where I search only the rows that have #N/A values
in the A column, and then return unique records of the resulting B column
values. And I need to have the entire line of 10 column shown in the subset.

So with that prep, can you tell me how I'd set up the advanced filter
dialogue?

Thx.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Debra, thanks for the reply. I am not sure I get which of the B2 suggestions
you mean. Am I supposed to leave it blank, or put the formula you entered?
Also, what is the simple explanation of what the formula is doing? I wanna
learn to fish, instead of just eating. :)

Thx.
--
Boris


"Debra Dalgleish" wrote:

To filter for the first record for each value in column B, where the
value in column A is #N/A --

In cell A1, enter the heading from cell A4
In cell A2, enter #N/A
Leave cell B2 blank
In cell B2, enter:
=SUMPRODUCT(--($B$4:$B5=B5),--(ISNA($A$4:$A5)))=1

Run the Advanced Filter, using cells A1:B2 as the criteria range.

BorisS wrote:
I'll try to clarify what I'd written.

I actually have 10 columns. First row has the headers (call them HeaderA1,
HeaderB1, etc.). Rows 2 and 3 are blank, waiting for the advanced filter
criteria (if I understand correctly how it's supposed to be set up). Row 4
has the headers again (HeaderA4, HeaderB4). All 11K records are below. I
need to have a subset list where I search only the rows that have #N/A values
in the A column, and then return unique records of the resulting B column
values. And I need to have the entire line of 10 column shown in the subset.

So with that prep, can you tell me how I'd set up the advanced filter
dialogue?

Thx.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

The blank cell is the heading (B1)
The formula is in the first criteria row (B2)

'===========================
To filter for the first record for each value in column B, where the
value in column A is #N/A --

In cell A1, enter the heading from cell A4
In cell A2, enter #N/A
Leave cell B1 blank
In cell B2, enter:
=SUMPRODUCT(--($B$4:$B5=B5),--(ISNA($A$4:$A5)))=1

Run the Advanced Filter, using cells A1:B2 as the criteria range.
'===========================

For an explanation of Sumproduct, see J.E. McGimpsey's site:
http://mcgimpsey.com/excel/formulae/doubleneg.html

and Bob Phillips' site:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

In this example, it's looking for the first instance of the value in
column B with an #N/A in column A.


BorisS wrote:
Debra, thanks for the reply. I am not sure I get which of the B2 suggestions
you mean. Am I supposed to leave it blank, or put the formula you entered?
Also, what is the simple explanation of what the formula is doing? I wanna
learn to fish, instead of just eating. :)

Thx.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html