How to filter a spreadsheet on multiple filter values
I've got a spreadsheet of addresses with the following columns: First Name, Last Name, Street Address, City, State, and zip code. I'd like to be able to filter or otherwise extract a subset of these address records based on a set of geographically close postal codes. We've been using http://www.freemaptools.com/find-zip-codes-inside-radius.htm to get a list of codes that are within a certain radius of the targe area. W
What's the easiest way to take the comma separated list of zipcodes from freemapstools.com to get a subset of addresses from the full mailing list spreadsheet?
For example: from our complete list of addresses, I want to see all addresses that have a zipcode in the set of: "55055,55119,55125,55129"
For what it's worth, we're using Google Docs, but if this would be easier in Excel that would be fine too.
spreadsheet
edited Aug 23 '14 at 22:32
niton
1,6941219
asked Nov 9 '11 at 21:35
DaveE
1614
add a comment |
I've got a spreadsheet of addresses with the following columns: First Name, Last Name, Street Address, City, State, and zip code. I'd like to be able to filter or otherwise extract a subset of these address records based on a set of geographically close postal codes. We've been using http://www.freemaptools.com/find-zip-codes-inside-radius.htm to get a list of codes that are within a certain radius of the targe area. W
What's the easiest way to take the comma separated list of zipcodes from freemapstools.com to get a subset of addresses from the full mailing list spreadsheet?
For example: from our complete list of addresses, I want to see all addresses that have a zipcode in the set of: "55055,55119,55125,55129"
For what it's worth, we're using Google Docs, but if this would be easier in Excel that would be fine too.
spreadsheet
edited Aug 23 '14 at 22:32
niton
1,6941219
asked Nov 9 '11 at 21:35
DaveE
1614
Thanks for the quick answers. Both suggestions work great if we're only trying to filter by a few zipcodes at a time. However, it's not uncommon for us to have a subset of 20 or 30 different zipcodes for an area. Manually checking or selecting those zipcodes one at a time is not very easy or fast. I'm hoping for a faster solution.
– DaveE
Nov 10 '11 at 3:24
add a comment |
I've got a spreadsheet of addresses with the following columns: First Name, Last Name, Street Address, City, State, and zip code. I'd like to be able to filter or otherwise extract a subset of these address records based on a set of geographically close postal codes. We've been using http://www.freemaptools.com/find-zip-codes-inside-radius.htm to get a list of codes that are within a certain radius of the targe area. W
What's the easiest way to take the comma separated list of zipcodes from freemapstools.com to get a subset of addresses from the full mailing list spreadsheet?
For example: from our complete list of addresses, I want to see all addresses that have a zipcode in the set of: "55055,55119,55125,55129"
For what it's worth, we're using Google Docs, but if this would be easier in Excel that would be fine too.
spreadsheet
edited Aug 23 '14 at 22:32
niton
1,6941219
asked Nov 9 '11 at 21:35
DaveE
1614
I've got a spreadsheet of addresses with the following columns: First Name, Last Name, Street Address, City, State, and zip code. I'd like to be able to filter or otherwise extract a subset of these address records based on a set of geographically close postal codes. We've been using http://www.freemaptools.com/find-zip-codes-inside-radius.htm to get a list of codes that are within a certain radius of the targe area. W
What's the easiest way to take the comma separated list of zipcodes from freemapstools.com to get a subset of addresses from the full mailing list spreadsheet?
For example: from our complete list of addresses, I want to see all addresses that have a zipcode in the set of: "55055,55119,55125,55129"
For what it's worth, we're using Google Docs, but if this would be easier in Excel that would be fine too.
spreadsheet
spreadsheet
edited Aug 23 '14 at 22:32
niton
1,6941219
asked Nov 9 '11 at 21:35
DaveE
1614
edited Aug 23 '14 at 22:32
niton
1,6941219
asked Nov 9 '11 at 21:35
DaveE
1614
edited Aug 23 '14 at 22:32
niton
1,6941219
edited Aug 23 '14 at 22:32
niton
1,6941219
edited Aug 23 '14 at 22:32
niton
1,6941219
1,6941219
asked Nov 9 '11 at 21:35
DaveE
1614
asked Nov 9 '11 at 21:35
DaveE
1614
asked Nov 9 '11 at 21:35
DaveE
1614
1614
Thanks for the quick answers. Both suggestions work great if we're only trying to filter by a few zipcodes at a time. However, it's not uncommon for us to have a subset of 20 or 30 different zipcodes for an area. Manually checking or selecting those zipcodes one at a time is not very easy or fast. I'm hoping for a faster solution.
– DaveE
Nov 10 '11 at 3:24
add a comment |
Thanks for the quick answers. Both suggestions work great if we're only trying to filter by a few zipcodes at a time. However, it's not uncommon for us to have a subset of 20 or 30 different zipcodes for an area. Manually checking or selecting those zipcodes one at a time is not very easy or fast. I'm hoping for a faster solution.
– DaveE
Nov 10 '11 at 3:24
Thanks for the quick answers. Both suggestions work great if we're only trying to filter by a few zipcodes at a time. However, it's not uncommon for us to have a subset of 20 or 30 different zipcodes for an area. Manually checking or selecting those zipcodes one at a time is not very easy or fast. I'm hoping for a faster solution.
– DaveE
Nov 10 '11 at 3:24
Thanks for the quick answers. Both suggestions work great if we're only trying to filter by a few zipcodes at a time. However, it's not uncommon for us to have a subset of 20 or 30 different zipcodes for an area. Manually checking or selecting those zipcodes one at a time is not very easy or fast. I'm hoping for a faster solution.
– DaveE
Nov 10 '11 at 3:24
add a comment |
3 Answers
3
active
oldest
votes
In your google doc spreadsheet, go to Data->Filter. Click on the boxed down arrow that appears in the header of the zip column. Click on "clear", then type in your desired zipcodes and click on them when they come up (they should be checked).
answered Nov 9 '11 at 22:09
Kevin
980617
I'm hoping there's a faster method as we often have 20+ zipcodes we'd like to filter by. typing and checking them one-by-one is pretty tedious.
– DaveE
Nov 10 '11 at 3:28
add a comment |
In Excel 2007 and 2010 the filter options use checkboxes on all applicable values to allow custom selection of any given combination for any given filtered column.
Instead of being presented with a list of the possible values and then whichever you select you get those filtered, such as in Office version 2003 and before, you get a series of checkboxes in 2007 and later. This is less efficient for those times that you only want one particular value, but it's much more powerful in the custom selection capability.
answered Nov 9 '11 at 22:21
music2myear
30.5k85597
I'm hoping there's a faster method as we often have 20+ zipcodes we'd like to filter by. Checking them one-by-one is pretty tedious.
– DaveE
Nov 10 '11 at 3:28
Yes it is. I assume it wouldn't be too difficult to write a macro that allows you to enter zip codes you wish to filter by. The current system is definitely an improvement from before, though it's more "average user"-friendly than it is "advanced user"-friendly.
– music2myear
Nov 10 '11 at 15:03
add a comment |
You can do something like the following - which uses a formula to filter, rather than the column's filter drop-down.
Cell F3 contains =FILTER(A3:A8, MATCH(A3:A8, D3:D5, 0)) where A contains the complete list of data, and D contains the subset used to filter.
Note that in Google Sheets, you just need to complete cell F3. F4 (and onwards) will automatically fill with the remaining results. Updating the filter output is easy - just edit column D.
I've included it in a single view for the sake of the screenshot below, however in practice I would put the output (columns F:G) in a separate sheet to delineate the two sets.
See Google Docs Help page for further info on the Filter function.
answered Mar 27 '17 at 11:28
Robyn
13812
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f355683%2fhow-to-filter-a-spreadsheet-on-multiple-filter-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
In your google doc spreadsheet, go to Data->Filter. Click on the boxed down arrow that appears in the header of the zip column. Click on "clear", then type in your desired zipcodes and click on them when they come up (they should be checked).
answered Nov 9 '11 at 22:09
Kevin
980617
I'm hoping there's a faster method as we often have 20+ zipcodes we'd like to filter by. typing and checking them one-by-one is pretty tedious.
– DaveE
Nov 10 '11 at 3:28
add a comment |
In your google doc spreadsheet, go to Data->Filter. Click on the boxed down arrow that appears in the header of the zip column. Click on "clear", then type in your desired zipcodes and click on them when they come up (they should be checked).
answered Nov 9 '11 at 22:09
Kevin
980617
I'm hoping there's a faster method as we often have 20+ zipcodes we'd like to filter by. typing and checking them one-by-one is pretty tedious.
– DaveE
Nov 10 '11 at 3:28
add a comment |
In your google doc spreadsheet, go to Data->Filter. Click on the boxed down arrow that appears in the header of the zip column. Click on "clear", then type in your desired zipcodes and click on them when they come up (they should be checked).
answered Nov 9 '11 at 22:09
Kevin
980617
In your google doc spreadsheet, go to Data->Filter. Click on the boxed down arrow that appears in the header of the zip column. Click on "clear", then type in your desired zipcodes and click on them when they come up (they should be checked).
answered Nov 9 '11 at 22:09
Kevin
980617
answered Nov 9 '11 at 22:09
Kevin
980617
answered Nov 9 '11 at 22:09
Kevin
980617
answered Nov 9 '11 at 22:09
Kevin
980617
980617
I'm hoping there's a faster method as we often have 20+ zipcodes we'd like to filter by. typing and checking them one-by-one is pretty tedious.
– DaveE
Nov 10 '11 at 3:28
add a comment |
I'm hoping there's a faster method as we often have 20+ zipcodes we'd like to filter by. typing and checking them one-by-one is pretty tedious.
– DaveE
Nov 10 '11 at 3:28
I'm hoping there's a faster method as we often have 20+ zipcodes we'd like to filter by. typing and checking them one-by-one is pretty tedious.
– DaveE
Nov 10 '11 at 3:28
I'm hoping there's a faster method as we often have 20+ zipcodes we'd like to filter by. typing and checking them one-by-one is pretty tedious.
– DaveE
Nov 10 '11 at 3:28
add a comment |
In Excel 2007 and 2010 the filter options use checkboxes on all applicable values to allow custom selection of any given combination for any given filtered column.
Instead of being presented with a list of the possible values and then whichever you select you get those filtered, such as in Office version 2003 and before, you get a series of checkboxes in 2007 and later. This is less efficient for those times that you only want one particular value, but it's much more powerful in the custom selection capability.
answered Nov 9 '11 at 22:21
music2myear
30.5k85597
I'm hoping there's a faster method as we often have 20+ zipcodes we'd like to filter by. Checking them one-by-one is pretty tedious.
– DaveE
Nov 10 '11 at 3:28
Yes it is. I assume it wouldn't be too difficult to write a macro that allows you to enter zip codes you wish to filter by. The current system is definitely an improvement from before, though it's more "average user"-friendly than it is "advanced user"-friendly.
– music2myear
Nov 10 '11 at 15:03
add a comment |
In Excel 2007 and 2010 the filter options use checkboxes on all applicable values to allow custom selection of any given combination for any given filtered column.
Instead of being presented with a list of the possible values and then whichever you select you get those filtered, such as in Office version 2003 and before, you get a series of checkboxes in 2007 and later. This is less efficient for those times that you only want one particular value, but it's much more powerful in the custom selection capability.
answered Nov 9 '11 at 22:21
music2myear
30.5k85597
I'm hoping there's a faster method as we often have 20+ zipcodes we'd like to filter by. Checking them one-by-one is pretty tedious.
– DaveE
Nov 10 '11 at 3:28
Yes it is. I assume it wouldn't be too difficult to write a macro that allows you to enter zip codes you wish to filter by. The current system is definitely an improvement from before, though it's more "average user"-friendly than it is "advanced user"-friendly.
– music2myear
Nov 10 '11 at 15:03
add a comment |
In Excel 2007 and 2010 the filter options use checkboxes on all applicable values to allow custom selection of any given combination for any given filtered column.
Instead of being presented with a list of the possible values and then whichever you select you get those filtered, such as in Office version 2003 and before, you get a series of checkboxes in 2007 and later. This is less efficient for those times that you only want one particular value, but it's much more powerful in the custom selection capability.
answered Nov 9 '11 at 22:21
music2myear
30.5k85597
In Excel 2007 and 2010 the filter options use checkboxes on all applicable values to allow custom selection of any given combination for any given filtered column.
Instead of being presented with a list of the possible values and then whichever you select you get those filtered, such as in Office version 2003 and before, you get a series of checkboxes in 2007 and later. This is less efficient for those times that you only want one particular value, but it's much more powerful in the custom selection capability.
answered Nov 9 '11 at 22:21
music2myear
30.5k85597
answered Nov 9 '11 at 22:21
music2myear
30.5k85597
answered Nov 9 '11 at 22:21
music2myear
30.5k85597
answered Nov 9 '11 at 22:21
music2myear
30.5k85597
30.5k85597
I'm hoping there's a faster method as we often have 20+ zipcodes we'd like to filter by. Checking them one-by-one is pretty tedious.
– DaveE
Nov 10 '11 at 3:28
Yes it is. I assume it wouldn't be too difficult to write a macro that allows you to enter zip codes you wish to filter by. The current system is definitely an improvement from before, though it's more "average user"-friendly than it is "advanced user"-friendly.
– music2myear
Nov 10 '11 at 15:03
add a comment |
I'm hoping there's a faster method as we often have 20+ zipcodes we'd like to filter by. Checking them one-by-one is pretty tedious.
– DaveE
Nov 10 '11 at 3:28
Yes it is. I assume it wouldn't be too difficult to write a macro that allows you to enter zip codes you wish to filter by. The current system is definitely an improvement from before, though it's more "average user"-friendly than it is "advanced user"-friendly.
– music2myear
Nov 10 '11 at 15:03
I'm hoping there's a faster method as we often have 20+ zipcodes we'd like to filter by. Checking them one-by-one is pretty tedious.
– DaveE
Nov 10 '11 at 3:28
I'm hoping there's a faster method as we often have 20+ zipcodes we'd like to filter by. Checking them one-by-one is pretty tedious.
– DaveE
Nov 10 '11 at 3:28
Yes it is. I assume it wouldn't be too difficult to write a macro that allows you to enter zip codes you wish to filter by. The current system is definitely an improvement from before, though it's more "average user"-friendly than it is "advanced user"-friendly.
– music2myear
Nov 10 '11 at 15:03
Yes it is. I assume it wouldn't be too difficult to write a macro that allows you to enter zip codes you wish to filter by. The current system is definitely an improvement from before, though it's more "average user"-friendly than it is "advanced user"-friendly.
– music2myear
Nov 10 '11 at 15:03
add a comment |
You can do something like the following - which uses a formula to filter, rather than the column's filter drop-down.
Cell F3 contains =FILTER(A3:A8, MATCH(A3:A8, D3:D5, 0)) where A contains the complete list of data, and D contains the subset used to filter.
Note that in Google Sheets, you just need to complete cell F3. F4 (and onwards) will automatically fill with the remaining results. Updating the filter output is easy - just edit column D.
I've included it in a single view for the sake of the screenshot below, however in practice I would put the output (columns F:G) in a separate sheet to delineate the two sets.
See Google Docs Help page for further info on the Filter function.
answered Mar 27 '17 at 11:28
Robyn
13812
add a comment |
You can do something like the following - which uses a formula to filter, rather than the column's filter drop-down.
Cell F3 contains =FILTER(A3:A8, MATCH(A3:A8, D3:D5, 0)) where A contains the complete list of data, and D contains the subset used to filter.
Note that in Google Sheets, you just need to complete cell F3. F4 (and onwards) will automatically fill with the remaining results. Updating the filter output is easy - just edit column D.
I've included it in a single view for the sake of the screenshot below, however in practice I would put the output (columns F:G) in a separate sheet to delineate the two sets.
See Google Docs Help page for further info on the Filter function.
answered Mar 27 '17 at 11:28
Robyn
13812
add a comment |
You can do something like the following - which uses a formula to filter, rather than the column's filter drop-down.
Cell F3 contains =FILTER(A3:A8, MATCH(A3:A8, D3:D5, 0)) where A contains the complete list of data, and D contains the subset used to filter.
Note that in Google Sheets, you just need to complete cell F3. F4 (and onwards) will automatically fill with the remaining results. Updating the filter output is easy - just edit column D.
I've included it in a single view for the sake of the screenshot below, however in practice I would put the output (columns F:G) in a separate sheet to delineate the two sets.
See Google Docs Help page for further info on the Filter function.
answered Mar 27 '17 at 11:28
Robyn
13812
You can do something like the following - which uses a formula to filter, rather than the column's filter drop-down.
Cell F3 contains =FILTER(A3:A8, MATCH(A3:A8, D3:D5, 0)) where A contains the complete list of data, and D contains the subset used to filter.
Note that in Google Sheets, you just need to complete cell F3. F4 (and onwards) will automatically fill with the remaining results. Updating the filter output is easy - just edit column D.
I've included it in a single view for the sake of the screenshot below, however in practice I would put the output (columns F:G) in a separate sheet to delineate the two sets.
See Google Docs Help page for further info on the Filter function.
answered Mar 27 '17 at 11:28
Robyn
13812
answered Mar 27 '17 at 11:28
Robyn
13812
answered Mar 27 '17 at 11:28
Robyn
13812
answered Mar 27 '17 at 11:28
Robyn
13812
13812
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f355683%2fhow-to-filter-a-spreadsheet-on-multiple-filter-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Thanks for the quick answers. Both suggestions work great if we're only trying to filter by a few zipcodes at a time. However, it's not uncommon for us to have a subset of 20 or 30 different zipcodes for an area. Manually checking or selecting those zipcodes one at a time is not very easy or fast. I'm hoping for a faster solution.
– DaveE
Nov 10 '11 at 3:24