Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-25-2018, 11:23 PM
MKTGCLOUD MKTGCLOUD is offline Filter or Lookup Formula to Organize Keywords Windows 10 Filter or Lookup Formula to Organize Keywords Office 2013
Novice
Filter or Lookup Formula to Organize Keywords
 
Join Date: Nov 2018
Posts: 5
MKTGCLOUD is on a distinguished road
Default Filter or Lookup Formula to Organize Keywords

I am coming up with a simple keyword distinguishing two categories of keywords for me to present to my client. Can anyone help me the formula to come up with just the keyword lists containing “Doctor” in the Doctor column and “Professor” in the Professor column?
Attached Files
File Type: xlsx Filter Doctor and Professor Worksheet.xlsx (20.1 KB, 12 views)
Reply With Quote
  #2  
Old 11-26-2018, 05:18 AM
ArviLaanemets ArviLaanemets is offline Filter or Lookup Formula to Organize Keywords Windows 8 Filter or Lookup Formula to Organize Keywords Office 2016
Expert
 
Join Date: May 2017
Posts: 725
ArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to behold
Default

Maybe a single column (and formula) will do?

Code:
=IF(
     IFERROR(1*(FIND("doctor",$A2)>0),0)+IFERROR(2*(FIND("professor",$A2)>0),0)=0;
     "",
     INDEX(
          {"doctor";"professor";"doctor and professor"},
          IFERROR(1*(FIND("doctor",$A2)>0),0)+IFERROR(2*(FIND("professor",$A2)>0),0)
     )
)
Reply With Quote
  #3  
Old 11-26-2018, 05:55 PM
MKTGCLOUD MKTGCLOUD is offline Filter or Lookup Formula to Organize Keywords Windows 10 Filter or Lookup Formula to Organize Keywords Office 2013
Novice
Filter or Lookup Formula to Organize Keywords
 
Join Date: Nov 2018
Posts: 5
MKTGCLOUD is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
Maybe a single column (and formula) will do?

Code:
=IF(
     IFERROR(1*(FIND("doctor",$A2)>0),0)+IFERROR(2*(FIND("professor",$A2)>0),0)=0;
     "",
     INDEX(
          {"doctor";"professor";"doctor and professor"},
          IFERROR(1*(FIND("doctor",$A2)>0),0)+IFERROR(2*(FIND("professor",$A2)>0),0)
     )
)
Thanks for the formula. This works on a singular column, but need to distinguish those keywords with a "doctor" and "professor" in it.
Reply With Quote
  #4  
Old 11-27-2018, 01:49 AM
xor xor is offline Filter or Lookup Formula to Organize Keywords Windows 10 Filter or Lookup Formula to Organize Keywords Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,080
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

What about
Attached Files
File Type: xlsx Doctor and Professor.xlsx (29.6 KB, 10 views)
Reply With Quote
  #5  
Old 11-27-2018, 02:31 AM
ArviLaanemets ArviLaanemets is offline Filter or Lookup Formula to Organize Keywords Windows 8 Filter or Lookup Formula to Organize Keywords Office 2016
Expert
 
Join Date: May 2017
Posts: 725
ArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to behold
Default

Quote:
Originally Posted by MKTGCLOUD View Post
... but need to distinguish those keywords with a "doctor" and "professor" in it.
The formula returns either "doctor", "professor", or "doctor and professor". You set the autofilter for table on - you always can get a list of entries having "doctor" in them by setting the filter for formula column to "doctor" and "doctor and professor", or a list of entries having "professor" in them by setting the filter for formula column to "professor" and "doctor and professor", etc.
Reply With Quote
  #6  
Old 11-27-2018, 07:35 PM
MKTGCLOUD MKTGCLOUD is offline Filter or Lookup Formula to Organize Keywords Windows 10 Filter or Lookup Formula to Organize Keywords Office 2013
Novice
Filter or Lookup Formula to Organize Keywords
 
Join Date: Nov 2018
Posts: 5
MKTGCLOUD is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
What about
Thanks for this. But, the formula only identifies which cell has the word “doctor” and/or “professor”.
Reply With Quote
  #7  
Old 11-27-2018, 07:37 PM
MKTGCLOUD MKTGCLOUD is offline Filter or Lookup Formula to Organize Keywords Windows 10 Filter or Lookup Formula to Organize Keywords Office 2013
Novice
Filter or Lookup Formula to Organize Keywords
 
Join Date: Nov 2018
Posts: 5
MKTGCLOUD is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
The formula returns either "doctor", "professor", or "doctor and professor". You set the autofilter for table on - you always can get a list of entries having "doctor" in them by setting the filter for formula column to "doctor" and "doctor and professor", or a list of entries having "professor" in them by setting the filter for formula column to "professor" and "doctor and professor", etc.
Thanks for the reply, Arvi. Can you attach your version of the worksheet so I can see and reference to?
Reply With Quote
  #8  
Old 11-28-2018, 12:13 AM
ArviLaanemets ArviLaanemets is offline Filter or Lookup Formula to Organize Keywords Windows 8 Filter or Lookup Formula to Organize Keywords Office 2016
Expert
 
Join Date: May 2017
Posts: 725
ArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to behold
Default

Here you have two variants of formula (for the second one I defined a Table and used Table Formula syntax)
Attached Files
File Type: xlsx Doctor and Professor Worksheet.xlsx (45.1 KB, 9 views)
Reply With Quote
  #9  
Old 11-28-2018, 07:35 PM
MKTGCLOUD MKTGCLOUD is offline Filter or Lookup Formula to Organize Keywords Windows 10 Filter or Lookup Formula to Organize Keywords Office 2013
Novice
Filter or Lookup Formula to Organize Keywords
 
Join Date: Nov 2018
Posts: 5
MKTGCLOUD is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
Here you have two variants of formula (for the second one I defined a Table and used Table Formula syntax)
Thanks for the Arvi. I checked and it worked to filter those keywords with a "doctor" or "professor" on it. Very helpful of you. Thanks again.
Reply With Quote
Reply

Tags
filter formula, lookup formula

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help with lookup formula tristanlau Excel 1 08-14-2017 07:16 AM
Formula help please (lookup across multiple sheets) froggybsb03 Excel 2 03-16-2017 02:16 AM
Filter or Lookup Formula to Organize Keywords lookup or filter data, update it and save it fre_ Excel Programming 3 06-06-2016 08:54 PM
LOOKUP - Complex lookup with 2 lookups in 1 cell sglandon Excel 6 05-05-2016 09:44 AM
Filter or Lookup Formula to Organize Keywords Best way to organize resources Steve. Project 3 05-17-2014 07:07 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:18 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft