Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
trouble with advanced filter
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
trouble with advanced filter
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
trouble with advanced filter
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
trouble with advanced filter
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
trouble with advanced filter
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
trouble with advanced filter
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem using Advanced filter | Excel Discussion (Misc queries) | |||
Hiding advanced filter | Excel Discussion (Misc queries) | |||
Refresh advanced filter | Excel Discussion (Misc queries) | |||
advanced filter | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |