Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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
?
135 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
8 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 50

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

580 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