Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
130 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

715 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