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
123 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:jvantassel1
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 7

Expert Comment

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

Author Comment

by:jvantassel1
Comment Utility
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 500 total points
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now