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
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
126 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 500 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 46

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

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…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

789 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