Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to create a drop down list to filter data in excel where there isn't an exact match between the drop down and the list

Posted on 2015-02-19
9
Medium Priority
?
133 Views
Last Modified: 2015-03-20
I have a "remarks" column in my excel document and want to be able to filter the list using a dropdown list.  there is not an exact match between the drop down and the list.
0
Comment
Question by:jvantassel1
  • 4
  • 3
9 Comments
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40619549
So, for example, if your remarks contain a certain word, you want to be able to filter by that?
0
 
LVL 1

Author Comment

by:jvantassel1
ID: 40619581
these are various remarks (shown below), I want to have a simple droplist (named range perhaps), i.e., AFTP, ATA, CIV DIS, JUMP
then filter my data set based on the selection.  Perhaps using the search function, though not sure...

FY15 1B CTC EXERCISES
FY15 1B ENL AFTP
FY15 1B ENL ATA
FY15 1B ENL CIV DIS
FY15 1B ENL JUMP
FY15 1B ENL P&A
FY15 1B ENL RMA
FY15 1B ENL SUBSISTENCE AFTP
FY15 1B OFF AFTP
FY15 1B OFF ATA
FY15 1B OFF CIV DIS
FY15 1B OFF JUMP
FY15 1B OFF P&A
FY15 1B OFF RMA
FY15 1B STATUTORY PAY ONLY
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40619601
On the Home ribbon, if you click Sort & Filter, then Filter, it will apply a filter that you can then click on and select Text Filters>>Contains.

Next to the drop down for contains, you can type in AFTP or anything else and hit OK.  This will apply that filter.  You can then edit the Contains field through those steps for whatever you want to filter by.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:jvantassel1
ID: 40619854
You're right, I am able to filter this way.  It's tedious, because I'm adding a "helper" column so I can easily pivot the data...
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40619861
Oh, I see.  Are you using =SEARCH("AFTP",A2) in the helper column?
0
 
LVL 1

Author Comment

by:jvantassel1
ID: 40619896
No, I just copied values into the cells.  There are 15 or so values to paste
0
 
LVL 7

Accepted Solution

by:
Katie Pierce earned 2000 total points
ID: 40619986
I don't know how extensive your list is, but here's a way to get all your filter items into one column so you can run a simple filter on them.

The first few columns will each be the item you're looking for, then the last column (F in the sample), will consolidate.  

The helper columns are using the FIND function to find the item, and the IF & ISERROR formulas to eliminate the #VALUE! result that FIND returns.  The Index formula moves through the helper columns looking for a value. Let me know if you want me to further clarify these.

The only caveat is that it presupposes that each remark contains only one of the items.  If a remark might have more than one, it'll need to go back to the drawing board.
Sample.xlsx
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40677594
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question