I often need to search a large list of search terms in an Excel sheet, but the search function is too tedious to do because I have to search for them individually. How do I search for these faster? ...
AbtinS I see my code only looks for cells which contain just the word you are looking for. This adjusted version looks for cells containing the words:
Option Explicit
Sub ListHits()
Dim Cell2Find As Range
Dim FoundCell As Range
Dim FirstFound As Range
Dim Ct As Long
Dim KeepLooking As Boolean
For Each Cell2Find In Worksheets("Keywords").UsedRange
If Len(Cell2Find.Value) > 0 Then
Set FirstFound = Nothing
With Worksheets("Data")
Ct = 0
Set FoundCell = Nothing
Set FoundCell = .UsedRange.Find(What:=Cell2Find.Value, After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If FoundCell Is Nothing Then
KeepLooking = False
Else
Set FirstFound = FoundCell
KeepLooking = True
End If
Do While KeepLooking
Ct = Ct + 1
Cell2Find.Offset(, Ct).Hyperlinks.Add Cell2Find.Offset(, Ct), "", FoundCell.Address(external:=True), FoundCell.Address(external:=True)
'Set FoundCell = Nothing
Set FoundCell = .UsedRange.FindNext(FoundCell)
If FoundCell Is Nothing Then KeepLooking = False
If FoundCell.Address = FirstFound.Address Then KeepLooking = False
Loop
End With
End If
Next
End Sub
AbtinS I see my code only looks for cells which contain just the word you are looking for. This adjusted version looks for cells containing the words:
Option Explicit
Sub ListHits()
Dim Cell2Find As Range
Dim FoundCell As Range
Dim FirstFound As Range
Dim Ct As Long
Dim KeepLooking As Boolean
For Each Cell2Find In Worksheets("Keywords").UsedRange
If Len(Cell2Find.Value) > 0 Then
Set FirstFound = Nothing
With Worksheets("Data")
Ct = 0
Set FoundCell = Nothing
Set FoundCell = .UsedRange.Find(What:=Cell2Find.Value, After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If FoundCell Is Nothing Then
KeepLooking = False
Else
Set FirstFound = FoundCell
KeepLooking = True
End If
Do While KeepLooking
Ct = Ct + 1
Cell2Find.Offset(, Ct).Hyperlinks.Add Cell2Find.Offset(, Ct), "", FoundCell.Address(external:=True), FoundCell.Address(external:=True)
'Set FoundCell = Nothing
Set FoundCell = .UsedRange.FindNext(FoundCell)
If FoundCell Is Nothing Then KeepLooking = False
If FoundCell.Address = FirstFound.Address Then KeepLooking = False
Loop
End With
End If
Next
End Sub
I am trying to look for words on a cell that with an specific criteria to search for example, if cells A2 contains AR to check cell B2 for certain words on cell X2, X3, X4,... that match it, the problem that I have about 34 different conditions to analyse it, I have tried with an excel formula but it throws a lot of false positives ( =SUMPRODUCT(--ISNUMBER(SEARCH($X2$:$X34$,B2)))>0 ) because I packed all the certain words on one list. I can't think of a way to isolate my search for all the 34 different conditions in a way to avoid the false positives bear in mind this excel doc has about 15k rows. I wonder if I could adjust my formula to be a bit more specific, I have tried with IF statements but it didn't work either, i wonder if there is a way to create a macro / vba to do it Many thanks
thanks , I have had a look into advanced filter and it could be useful but I kind of need to know the location of the row item which contains the word combination, rather than just have the word returned as it is part of a bigger data set. I have taken out the two columns of narrative text which I need searched from the main data set by using a pivot table where they are displayed in one column. I'm quite confident I can find the original line items again so currently the data I need searched is all in one column but across ~95000 rows. Can you help? thanks
Copy the code from my post, open the Excel file, Alt+F11 to go to the VBA editor. Find a module in the list of modules in the small tree on the left that contains the "old" code and replace all of it with the bit you copied.
JKPieterse How do I update the spreadsheet to include the new code?
Resources
"}},"componentScriptGroups({\"componentId\":\"custom.widget.MicrosoftFooter\"})":{"__typename":"ComponentScriptGroups","scriptGroups":{"__typename":"ComponentScriptGroupsDefinition","afterInteractive":{"__typename":"PageScriptGroupDefinition","group":"AFTER_INTERACTIVE","scriptIds":[]},"lazyOnLoad":{"__typename":"PageScriptGroupDefinition","group":"LAZY_ON_LOAD","scriptIds":[]}},"componentScripts":[]},"messages({\"constraints\":{\"solution\":{\"eq\":true},\"topicId\":{\"eq\":\"message:1307297\"}},\"first\":10,\"sorts\":{\"postTime\":{\"direction\":\"ASC\"}}})":{"__typename":"MessageConnection","edges":[{"__typename":"MessageEdge","cursor":"MjUuM3wyLjF8aXwxMHwxMzI6MXxpbnQsMTM0MDAxMiwxMzQwMDEy","node":{"__ref":"AcceptedSolutionMessage:message:1340012"}}],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null},"totalCount":1},"cachedText({\"lastModified\":\"1745505307000\",\"locale\":\"en-US\",\"namespaces\":[\"components/community/NavbarDropdownToggle\"]})":[{"__ref":"CachedAsset:text:en_US-components/community/NavbarDropdownToggle-1745505307000"}],"cachedText({\"lastModified\":\"1745505307000\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/users/UserAvatar\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/users/UserAvatar-1745505307000"}],"cachedText({\"lastModified\":\"1745505307000\",\"locale\":\"en-US\",\"namespaces\":[\"shared/client/components/ranks/UserRankLabel\"]})":[{"__ref":"CachedAsset:text:en_US-shared/client/components/ranks/UserRankLabel-1745505307000"}],"cachedText({\"lastModified\":\"1745505307000\",\"locale\":\"en-US\",\"namespaces\":[\"components/tags/TagView/TagViewChip\"]})":[{"__ref":"CachedAsset:text:en_US-components/tags/TagView/TagViewChip-1745505307000"}],"cachedText({\"lastModified\":\"1745505307000\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/AcceptedSolutionButton\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/AcceptedSolutionButton-1745505307000"}],"cachedText({\"lastModified\":\"1745505307000\",\"locale\":\"en-US\",\"namespaces\":[\"components/messages/LinearReplyList\"]})":[{"__ref":"CachedAsset:text:en_US-components/messages/LinearReplyList-1745505307000"}],"message({\"id\":\"message:1338806\"})":{"__ref":"ForumReplyMessage:message:1338806"},"cachedText({\"lastModified\":\"1745505307000\",\"locale\":\"en-US\",\"namespaces\":[\"components/attachments/AttachmentView/AttachmentViewChip\"]})":[{"__ref":"CachedAsset:text:en_US-components/attachments/AttachmentView/AttachmentViewChip-1745505307000"}],"message({\"id\":\"message:3013427\"})":{"__ref":"ForumReplyMessage:message:3013427"},"message({\"id\":\"message:3012568\"})":{"__ref":"ForumReplyMessage:message:3012568"},"message({\"id\":\"message:2166468\"})":{"__ref":"ForumReplyMessage:message:2166468"},"message({\"id\":\"message:2093118\"})":{"__ref":"ForumReplyMessage:message:2093118"},"message({\"id\":\"message:2081641\"})":{"__ref":"ForumReplyMessage:message:2081641"},"message({\"id\":\"message:2081412\"})":{"__ref":"ForumReplyMessage:message:2081412"},"message({\"id\":\"message:1348013\"})":{"__ref":"ForumReplyMessage:message:1348013"},"message({\"id\":\"message:1346525\"})":{"__ref":"ForumReplyMessage:message:1346525"},"message({\"id\":\"message:1346082\"})":{"__ref":"ForumReplyMessage:message:1346082"}},"Theme:customTheme1":{"__typename":"Theme","id":"customTheme1"},"User:user:-1":{"__typename":"User","id":"user:-1","uid":-1,"login":"Deleted","email":"","avatar":null,"rank":null,"kudosWeight":1,"registrationData":{"__typename":"RegistrationData","status":"ANONYMOUS","registrationTime":null,"confirmEmailStatus":false,"registrationAccessLevel":"VIEW","ssoRegistrationFields":[]},"ssoId":null,"profileSettings":{"__typename":"ProfileSettings","dateDisplayStyle":{"__typename":"InheritableStringSettingWithPossibleValues","key":"layout.friendly_dates_enabled","value":"false","localValue":"true","possibleValues":["true","false"]},"dateDisplayFormat":{"__typename":"InheritableStringSetting","key":"layout.format_pattern_date","value":"MMM dd yyyy","localValue":"MM-dd-yyyy"},"language":{"__typename":"InheritableStringSettingWithPossibleValues","key":"profile.language","value":"en-US","localValue":null,"possibleValues":["en-US","es-ES"]},"repliesSortOrder":{"__typename":"InheritableStringSettingWithPossibleValues","key":"config.user_replies_sort_order","value":"DEFAULT","localValue":"DEFAULT","possibleValues":["DEFAULT","LIKES","PUBLISH_TIME","REVERSE_PUBLISH_TIME"]}},"deleted":false},"CachedAsset:pages-1746563594225":{"__typename":"CachedAsset","id":"pages-1746563594225","value":[{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"BlogViewAllPostsPage","type":"BLOG","urlPath":"/category/:categoryId/blog/:boardId/all-posts/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"CasePortalPage","type":"CASE_PORTAL","urlPath":"/caseportal","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"CreateGroupHubPage","type":"GROUP_HUB","urlPath":"/groups/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"CaseViewPage","type":"CASE_DETAILS","urlPath":"/case/:caseId/:caseNumber","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"InboxPage","type":"COMMUNITY","urlPath":"/inbox","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"HelpFAQPage","type":"COMMUNITY","urlPath":"/help","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"IdeaMessagePage","type":"IDEA_POST","urlPath":"/idea/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"IdeaViewAllIdeasPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId/all-ideas/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"LoginPage","type":"USER","urlPath":"/signin","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"BlogPostPage","type":"BLOG","urlPath":"/category/:categoryId/blogs/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"UserBlogPermissions.Page","type":"COMMUNITY","urlPath":"/c/user-blog-permissions/page","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"ThemeEditorPage","type":"COMMUNITY","urlPath":"/designer/themes","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"TkbViewAllArticlesPage","type":"TKB","urlPath":"/category/:categoryId/kb/:boardId/all-articles/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730819800000,"localOverride":null,"page":{"id":"AllEvents","type":"CUSTOM","urlPath":"/Events","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"OccasionEditPage","type":"EVENT","urlPath":"/event/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"OAuthAuthorizationAllowPage","type":"USER","urlPath":"/auth/authorize/allow","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"PageEditorPage","type":"COMMUNITY","urlPath":"/designer/pages","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"PostPage","type":"COMMUNITY","urlPath":"/category/:categoryId/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"ForumBoardPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"TkbBoardPage","type":"TKB","urlPath":"/category/:categoryId/kb/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"EventPostPage","type":"EVENT","urlPath":"/category/:categoryId/events/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"UserBadgesPage","type":"COMMUNITY","urlPath":"/users/:login/:userId/badges","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"GroupHubMembershipAction","type":"GROUP_HUB","urlPath":"/membership/join/:nodeId/:membershipType","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"MaintenancePage","type":"COMMUNITY","urlPath":"/maintenance","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"IdeaReplyPage","type":"IDEA_REPLY","urlPath":"/idea/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"UserSettingsPage","type":"USER","urlPath":"/mysettings/:userSettingsTab","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"GroupHubsPage","type":"GROUP_HUB","urlPath":"/groups","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"ForumPostPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"OccasionRsvpActionPage","type":"OCCASION","urlPath":"/event/:boardId/:messageSubject/:messageId/rsvp/:responseType","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"VerifyUserEmailPage","type":"USER","urlPath":"/verifyemail/:userId/:verifyEmailToken","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"AllOccasionsPage","type":"OCCASION","urlPath":"/category/:categoryId/events/:boardId/all-events/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"EventBoardPage","type":"EVENT","urlPath":"/category/:categoryId/events/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"TkbReplyPage","type":"TKB_REPLY","urlPath":"/kb/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"IdeaBoardPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"CommunityGuideLinesPage","type":"COMMUNITY","urlPath":"/communityguidelines","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"CaseCreatePage","type":"SALESFORCE_CASE_CREATION","urlPath":"/caseportal/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"TkbEditPage","type":"TKB","urlPath":"/kb/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"ForgotPasswordPage","type":"USER","urlPath":"/forgotpassword","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"IdeaEditPage","type":"IDEA","urlPath":"/idea/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"TagPage","type":"COMMUNITY","urlPath":"/tag/:tagName","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"BlogBoardPage","type":"BLOG","urlPath":"/category/:categoryId/blog/:boardId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"OccasionMessagePage","type":"OCCASION_TOPIC","urlPath":"/event/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"ManageContentPage","type":"COMMUNITY","urlPath":"/managecontent","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"ClosedMembershipNodeNonMembersPage","type":"GROUP_HUB","urlPath":"/closedgroup/:groupHubId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"CommunityPage","type":"COMMUNITY","urlPath":"/","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"ForumMessagePage","type":"FORUM_TOPIC","urlPath":"/discussions/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"IdeaPostPage","type":"IDEA","urlPath":"/category/:categoryId/ideas/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730819800000,"localOverride":null,"page":{"id":"CommunityHub.Page","type":"CUSTOM","urlPath":"/Directory","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"BlogMessagePage","type":"BLOG_ARTICLE","urlPath":"/blog/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"RegistrationPage","type":"USER","urlPath":"/register","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"EditGroupHubPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"ForumEditPage","type":"FORUM","urlPath":"/discussions/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"ResetPasswordPage","type":"USER","urlPath":"/resetpassword/:userId/:resetPasswordToken","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1730819800000,"localOverride":null,"page":{"id":"AllBlogs.Page","type":"CUSTOM","urlPath":"/blogs","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"TkbMessagePage","type":"TKB_ARTICLE","urlPath":"/kb/:boardId/:messageSubject/:messageId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"BlogEditPage","type":"BLOG","urlPath":"/blog/:boardId/:messageSubject/:messageId/edit","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"ManageUsersPage","type":"USER","urlPath":"/users/manage/:tab?/:manageUsersTab?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"ForumReplyPage","type":"FORUM_REPLY","urlPath":"/discussions/:boardId/:messageSubject/:messageId/replies/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"PrivacyPolicyPage","type":"COMMUNITY","urlPath":"/privacypolicy","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"NotificationPage","type":"COMMUNITY","urlPath":"/notifications","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"UserPage","type":"USER","urlPath":"/users/:login/:userId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"HealthCheckPage","type":"COMMUNITY","urlPath":"/health","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"OccasionReplyPage","type":"OCCASION_REPLY","urlPath":"/event/:boardId/:messageSubject/:messageId/comments/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"ManageMembersPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/manage/:tab?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"SearchResultsPage","type":"COMMUNITY","urlPath":"/search","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"BlogReplyPage","type":"BLOG_REPLY","urlPath":"/blog/:boardId/:messageSubject/:messageId/replies/:replyId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"GroupHubPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"TermsOfServicePage","type":"COMMUNITY","urlPath":"/termsofservice","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"CategoryPage","type":"CATEGORY","urlPath":"/category/:categoryId","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"ForumViewAllTopicsPage","type":"FORUM","urlPath":"/category/:categoryId/discussions/:boardId/all-topics/(/:after|/:before)?","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"TkbPostPage","type":"TKB","urlPath":"/category/:categoryId/kbs/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"},{"lastUpdatedTime":1746563594225,"localOverride":null,"page":{"id":"GroupHubPostPage","type":"GROUP_HUB","urlPath":"/group/:groupHubId/:boardId/create","__typename":"PageDescriptor"},"__typename":"PageResource"}],"localOverride":false},"CachedAsset:text:en_US-components/context/AppContext/AppContextProvider-0":{"__typename":"CachedAsset","id":"text:en_US-components/context/AppContext/AppContextProvider-0","value":{"noCommunity":"Cannot find community","noUser":"Cannot find current user","noNode":"Cannot find node with id {nodeId}","noMessage":"Cannot find message with id {messageId}","userBanned":"We're sorry, but you have been banned from using this site.","userBannedReason":"You have been banned for the following reason: {reason}"},"localOverride":false},"CachedAsset:text:en_US-shared/client/components/common/Loading/LoadingDot-0":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/common/Loading/LoadingDot-0","value":{"title":"Loading..."},"localOverride":false},"Rank:rank:37":{"__typename":"Rank","id":"rank:37","position":18,"name":"Copper Contributor","color":"333333","icon":null,"rankStyle":"TEXT"},"User:user:311343":{"__typename":"User","id":"user:311343","uid":311343,"login":"AbtinS","deleted":false,"avatar":{"__typename":"UserAvatar","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/m_assets/avatars/default/avatar-5.svg?time=0"},"rank":{"__ref":"Rank:rank:37"},"email":"","messagesCount":9,"biography":null,"topicsCount":2,"kudosReceivedCount":1,"kudosGivenCount":2,"kudosWeight":1,"registrationData":{"__typename":"RegistrationData","status":null,"registrationTime":"2019-03-31T08:46:46.644-07:00","confirmEmailStatus":null},"followersCount":null,"solutionsCount":0,"entityType":"USER","eventPath":"community:gxcuf89792/user:311343"},"Category:category:microsoft365":{"__typename":"Category","id":"category:microsoft365","entityType":"CATEGORY","displayId":"microsoft365","nodeType":"category","depth":3,"title":"Microsoft 365","shortTitle":"Microsoft 365","parent":{"__ref":"Category:category:products-services"},"categoryPolicies":{"__typename":"CategoryPolicies","canReadNode":{"__typename":"PolicyResult","failureReason":null}}},"Category:category:top":{"__typename":"Category","id":"category:top","entityType":"CATEGORY","displayId":"top","nodeType":"category","depth":0,"title":"Top","shortTitle":"Top"},"Category:category:communities":{"__typename":"Category","id":"category:communities","entityType":"CATEGORY","displayId":"communities","nodeType":"category","depth":1,"parent":{"__ref":"Category:category:top"},"title":"Communities","shortTitle":"Communities"},"Category:category:products-services":{"__typename":"Category","id":"category:products-services","entityType":"CATEGORY","displayId":"products-services","nodeType":"category","depth":2,"parent":{"__ref":"Category:category:communities"},"title":"Products","shortTitle":"Products"},"Forum:board:ExcelGeneral":{"__typename":"Forum","id":"board:ExcelGeneral","entityType":"FORUM","displayId":"ExcelGeneral","nodeType":"board","depth":4,"conversationStyle":"FORUM","repliesProperties":{"__typename":"RepliesProperties","sortOrder":"REVERSE_PUBLISH_TIME","repliesFormat":"threaded"},"tagProperties":{"__typename":"TagNodeProperties","tagsEnabled":{"__typename":"PolicyResult","failureReason":null}},"requireTags":true,"tagType":"PRESET_ONLY","description":"Your community for how-to discussions and sharing best practices on Microsoft Excel. If you’re looking for technical support, please visit Microsoft Support Community.","title":"Excel","shortTitle":"Excel","parent":{"__ref":"Category:category:microsoft365"},"ancestors":{"__typename":"CoreNodeConnection","edges":[{"__typename":"CoreNodeEdge","node":{"__ref":"Community:community:gxcuf89792"}},{"__typename":"CoreNodeEdge","node":{"__ref":"Category:category:communities"}},{"__typename":"CoreNodeEdge","node":{"__ref":"Category:category:products-services"}},{"__typename":"CoreNodeEdge","node":{"__ref":"Category:category:microsoft365"}}]},"userContext":{"__typename":"NodeUserContext","canAddAttachments":false,"canUpdateNode":false,"canPostMessages":false,"isSubscribed":false},"theme":{"__ref":"Theme:customTheme1"},"boardPolicies":{"__typename":"BoardPolicies","canViewSpamDashBoard":{"__typename":"PolicyResult","failureReason":{"__typename":"FailureReason","message":"error.lithium.policies.feature.moderation_spam.action.access_spam_quarantine.allowed.accessDenied","key":"error.lithium.policies.feature.moderation_spam.action.access_spam_quarantine.allowed.accessDenied","args":[]}},"canArchiveMessage":{"__typename":"PolicyResult","failureReason":{"__typename":"FailureReason","message":"error.lithium.policies.content_archivals.enable_content_archival_settings.accessDenied","key":"error.lithium.policies.content_archivals.enable_content_archival_settings.accessDenied","args":[]}},"canPublishArticleOnCreate":{"__typename":"PolicyResult","failureReason":{"__typename":"FailureReason","message":"error.lithium.policies.forums.policy_can_publish_on_create_workflow_action.accessDenied","key":"error.lithium.policies.forums.policy_can_publish_on_create_workflow_action.accessDenied","args":[]}}},"eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/"},"ForumTopicMessage:message:1307297":{"__typename":"ForumTopicMessage","uid":1307297,"subject":"How do I find multiple search terms in Excel?","id":"message:1307297","revisionNum":1,"repliesCount":22,"author":{"__ref":"User:user:311343"},"depth":0,"hasGivenKudo":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"conversation":{"__ref":"Conversation:conversation:1307297"},"readOnly":false,"editFrozen":false,"moderationData":{"__ref":"ModerationData:moderation_data:1307297"},"body@stripHtml({\"truncateLength\":200})":" I often need to search a large list of search terms in an Excel sheet, but the search function is too tedious to do because I have to search for them individually. How do I search for these faster? ...","body@stringLength":"579","rawBody":"
I often need to search a large list of search terms in an Excel sheet, but the search function is too tedious to do because I have to search for them individually. How do I search for these faster? I don't need to search a specific column or columns, just the entire sheet is fine. The list of search terms I get are not in any particular format, usually just a list typed up in Microsoft word or in PDF format, but I am able to copy and paste them. I'm normally using Excel for Android, free version, but also have a laptop with Office 2010.
","kudosSumWeight":0,"postTime":"2020-04-14T14:04:48.274-07:00","images":{"__typename":"AssociatedImageConnection","edges":[],"totalCount":0,"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}},"attachments":{"__typename":"AttachmentConnection","pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null},"edges":[]},"tags":{"__typename":"TagConnection","pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null},"edges":[{"__typename":"TagEdge","cursor":"MjUuM3wyLjF8b3wxMHxfTlZffDE","node":{"__typename":"Tag","id":"tag:excel","text":"excel","time":"2016-06-21T14:08:51.129-07:00","lastActivityTime":null,"messagesCount":null,"followersCount":null}}]},"timeToRead":1,"currentRevision":{"__ref":"Revision:revision:1307297_1"},"latestVersion":null,"metrics":{"__typename":"MessageMetrics","views":260130},"visibilityScope":"PUBLIC","canonicalUrl":null,"seoTitle":null,"seoDescription":null,"isEscalated":null,"placeholder":false,"originalMessageForPlaceholder":null,"messagePolicies":{"__typename":"MessagePolicies","canModerateSpamMessage":{"__typename":"PolicyResult","failureReason":{"__typename":"FailureReason","message":"error.lithium.policies.feature.moderation_spam.action.moderate_entity.allowed.accessDenied","key":"error.lithium.policies.feature.moderation_spam.action.moderate_entity.allowed.accessDenied","args":[]}}},"archivalData":null,"customFields":[]},"Conversation:conversation:1307297":{"__typename":"Conversation","id":"conversation:1307297","solved":true,"topic":{"__ref":"ForumTopicMessage:message:1307297"},"lastPostingActivityTime":"2022-03-10T02:36:11.260-08:00","lastPostTime":"2022-03-10T02:36:11.260-08:00","unreadReplyCount":22,"isSubscribed":false},"ModerationData:moderation_data:1307297":{"__typename":"ModerationData","id":"moderation_data:1307297","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"Revision:revision:1307297_1":{"__typename":"Revision","id":"revision:1307297_1","lastEditTime":"2020-04-14T14:04:48.274-07:00"},"AcceptedSolutionMessage:message:1340012":{"__typename":"AcceptedSolutionMessage","id":"message:1340012","conversation":{"__ref":"Conversation:conversation:1307297"},"author":{"__ref":"User:user:22322"},"revisionNum":1,"uid":1340012,"depth":9,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:1338806"},"subject":"Re: How do I find multiple search terms in Excel?","moderationData":{"__ref":"ModerationData:moderation_data:1340012"},"body":"
AbtinS I see my code only looks for cells which contain just the word you are looking for. This adjusted version looks for cells containing the words:
\n
Option Explicit\n\nSub ListHits()\n Dim Cell2Find As Range\n Dim FoundCell As Range\n Dim FirstFound As Range\n Dim Ct As Long\n Dim KeepLooking As Boolean\n For Each Cell2Find In Worksheets(\"Keywords\").UsedRange\n If Len(Cell2Find.Value) > 0 Then\n Set FirstFound = Nothing\n With Worksheets(\"Data\")\n Ct = 0\n Set FoundCell = Nothing\n Set FoundCell = .UsedRange.Find(What:=Cell2Find.Value, After:=.Range(\"A1\"), LookIn:=xlValues, _\n LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _\n MatchCase:=False, SearchFormat:=False)\n\n If FoundCell Is Nothing Then\n KeepLooking = False\n Else\n Set FirstFound = FoundCell\n KeepLooking = True\n End If\n Do While KeepLooking\n Ct = Ct + 1\n Cell2Find.Offset(, Ct).Hyperlinks.Add Cell2Find.Offset(, Ct), \"\", FoundCell.Address(external:=True), FoundCell.Address(external:=True)\n 'Set FoundCell = Nothing\n Set FoundCell = .UsedRange.FindNext(FoundCell)\n If FoundCell Is Nothing Then KeepLooking = False\n If FoundCell.Address = FirstFound.Address Then KeepLooking = False\n Loop\n End With\n End If\n Next\nEnd Sub\n
","isEscalated":null,"postTime":"2020-04-26T08:32:39.369-07:00","solution":false,"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[{"__typename":"AttachmentEdge","cursor":"MjUuM3wyLjF8b3w1fF9OVl98MQ","node":{"__ref":"Attachment:{\"id\":\"attachment:message1338806AttachmentNumber1\",\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/60178/1/FindListOfWords2.xlsm\"}"}}],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"ModerationData:moderation_data:1340012":{"__typename":"ModerationData","id":"moderation_data:1340012","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"Rank:rank:32":{"__typename":"Rank","id":"rank:32","position":13,"name":"Silver Contributor","color":"333333","icon":null,"rankStyle":"TEXT"},"ModerationData:moderation_data:1338806":{"__typename":"ModerationData","id":"moderation_data:1338806","status":"APPROVED","rejectReason":null},"Revision:revision:1340012_1":{"__typename":"Revision","id":"revision:1340012_1","lastEditTime":"2020-04-27T02:55:35.848-07:00"},"QueryVariables:ReplyList:message:1340012:1":{"__typename":"QueryVariables","id":"ReplyList:message:1340012:1","value":{"id":"message:1340012","sorts":{"postTime":{"direction":"DESC"}},"useAvatar":true,"useAuthorLogin":true,"useAuthorRank":true,"useBody":true,"useKudosCount":true,"useTimeToRead":false,"useMedia":false,"useReadOnlyIcon":false,"useRepliesCount":false,"useSearchSnippet":false,"useAcceptedSolutionButton":true,"useSolvedBadge":false,"useAttachments":false,"attachmentsFirst":5,"useTags":false,"useNodeAncestors":false,"useUserHoverCard":false,"useNodeHoverCard":false,"useModerationStatus":true,"usePreviewSubjectModal":false,"useMessageStatus":true}},"CachedAsset:text:en_US-components/community/NavbarDropdownToggle-1745505307000":{"__typename":"CachedAsset","id":"text:en_US-components/community/NavbarDropdownToggle-1745505307000","value":{"ariaLabelClosed":"Press the down arrow to open the menu"},"localOverride":false},"CachedAsset:text:en_US-shared/client/components/users/UserAvatar-1745505307000":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/users/UserAvatar-1745505307000","value":{"altText":"{login}'s avatar","altTextGeneric":"User's avatar"},"localOverride":false},"CachedAsset:text:en_US-shared/client/components/ranks/UserRankLabel-1745505307000":{"__typename":"CachedAsset","id":"text:en_US-shared/client/components/ranks/UserRankLabel-1745505307000","value":{"altTitle":"Icon for {rankName} rank"},"localOverride":false},"CachedAsset:text:en_US-components/tags/TagView/TagViewChip-1745505307000":{"__typename":"CachedAsset","id":"text:en_US-components/tags/TagView/TagViewChip-1745505307000","value":{"tagLabelName":"Tag name {tagName}"},"localOverride":false},"CachedAsset:text:en_US-components/messages/AcceptedSolutionButton-1745505307000":{"__typename":"CachedAsset","id":"text:en_US-components/messages/AcceptedSolutionButton-1745505307000","value":{"accept":"Mark as Solution","accepted":"Marked as Solution","errorHeader":"Error!","errorAdd":"There was an error marking as solution.","errorRemove":"There was an error unmarking as solution.","solved":"Solved","topicAlreadySolvedErrorTitle":"Solution Already Exists","topicAlreadySolvedErrorDesc":"Refresh the browser to view the existing solution"},"localOverride":false},"CachedAsset:text:en_US-components/messages/LinearReplyList-1745505307000":{"__typename":"CachedAsset","id":"text:en_US-components/messages/LinearReplyList-1745505307000","value":{"title":"{count, plural, one{# Reply} other{# Replies}}","title@board:BLOG":"{count, plural, one{# Comment} other{# Comments}}","title@board:TKB":"{count, plural, one{# Comment} other{# Comments}}","title@board:IDEA":"{count, plural, one{# Comment} other{# Comments}}","title@board:OCCASION":"{count, plural, one{# Comment} other{# Comments}}","noRepliesTitle":"No Replies","noRepliesTitle@board:BLOG":"No Comments","noRepliesTitle@board:TKB":"No Comments","noRepliesTitle@board:IDEA":"No Comments","noRepliesTitle@board:OCCASION":"No Comments","noRepliesDescription":"Be the first to reply","noRepliesDescription@board:BLOG":"Be the first to comment","noRepliesDescription@board:TKB":"Be the first to comment","noRepliesDescription@board:OCCASION":"Be the first to comment","noRepliesDescription@board:IDEA":"Be the first to comment","messageReadOnlyAlert:BLOG":"Comments have been turned off for this post","messageReadOnlyAlert:TKB":"Comments have been turned off for this article","messageReadOnlyAlert:IDEA":"Comments have been turned off for this idea","messageReadOnlyAlert:FORUM":"Replies have been turned off for this discussion","messageReadOnlyAlert:OCCASION":"Comments have been turned off for this event"},"localOverride":false},"Attachment:{\"id\":\"attachment:message1338806AttachmentNumber1\",\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/60178/1/FindListOfWords2.xlsm\"}":{"__typename":"Attachment","id":"attachment:message1338806AttachmentNumber1","filename":"FindListOfWords2.xlsm","filesize":331277,"contentType":"application/vnd.ms-excel.sheet.macroEnabled.12","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/60178/1/FindListOfWords2.xlsm"},"User:user:1231870":{"__typename":"User","id":"user:1231870","deleted":false,"uid":1231870,"login":"AlexA007","biography":null,"registrationData":{"__typename":"RegistrationData","status":null,"registrationTime":"2021-11-28T15:42:20.178-08:00"},"email":"","avatar":{"__typename":"UserAvatar","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/m_assets/avatars/default/avatar-1.svg?time=0"},"rank":{"__ref":"Rank:rank:37"},"entityType":"USER","eventPath":"community:gxcuf89792/user:1231870"},"ForumReplyMessage:message:3012568":{"__typename":"ForumReplyMessage","uid":3012568,"id":"message:3012568","revisionNum":1,"author":{"__ref":"User:user:1231870"},"readOnly":false,"depth":10,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"AcceptedSolutionMessage:message:1340012"},"conversation":{"__ref":"Conversation:conversation:1307297"},"subject":"Re: How do I find multiple search terms in Excel?","moderationData":{"__ref":"ModerationData:moderation_data:3012568"},"body":"
I am trying to look for words on a cell that with an specific criteria to search for example, if cells A2 contains AR to check cell B2 for certain words on cell X2, X3, X4,... that match it, the problem that I have about 34 different conditions to analyse it, I have tried with an excel formula but it throws a lot of false positives ( =SUMPRODUCT(--ISNUMBER(SEARCH($X2$:$X34$,B2)))>0 ) because I packed all the certain words on one list. I can't think of a way to isolate my search for all the 34 different conditions in a way to avoid the false positives bear in mind this excel doc has about 15k rows. I wonder if I could adjust my formula to be a bit more specific, I have tried with IF statements but it didn't work either, i wonder if there is a way to create a macro / vba to do it Many thanks
","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"213","kudosSumWeight":0,"postTime":"2021-11-28T16:15:12.335-08:00","lastPublishTime":"2021-11-28T16:15:12.335-08:00","metrics":{"__typename":"MessageMetrics","views":183097},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:1307297/message:3012568","repliesCount":1,"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"ModerationData:moderation_data:3013427":{"__typename":"ModerationData","id":"moderation_data:3013427","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ForumReplyMessage:message:3013427":{"__typename":"ForumReplyMessage","author":{"__ref":"User:user:22322"},"id":"message:3013427","revisionNum":1,"uid":3013427,"depth":11,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:3012568"},"conversation":{"__ref":"Conversation:conversation:1307297"},"subject":"Re: How do I find multiple search terms in Excel?","moderationData":{"__ref":"ModerationData:moderation_data:3013427"},"body":"Hi AlexA007 Can you post an Excel file with some (anonymized) sample data in which you highlight the expected outcome?","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"118","kudosSumWeight":0,"postTime":"2021-11-29T01:16:42.989-08:00","lastPublishTime":"2021-11-29T01:16:42.989-08:00","metrics":{"__typename":"MessageMetrics","views":8228},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:1307297/message:3013427","repliesCount":0,"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"ModerationData:moderation_data:3012568":{"__typename":"ModerationData","id":"moderation_data:3012568","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"User:user:979011":{"__typename":"User","id":"user:979011","uid":979011,"login":"abdallahs","biography":null,"registrationData":{"__typename":"RegistrationData","status":null,"registrationTime":"2021-02-25T10:21:12.076-08:00"},"deleted":false,"email":"","avatar":{"__typename":"UserAvatar","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/m_assets/avatars/default/avatar-9.svg?time=0"},"rank":{"__ref":"Rank:rank:37"},"entityType":"USER","eventPath":"community:gxcuf89792/user:979011"},"ModerationData:moderation_data:2166468":{"__typename":"ModerationData","id":"moderation_data:2166468","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ForumReplyMessage:message:2166468":{"__typename":"ForumReplyMessage","author":{"__ref":"User:user:979011"},"id":"message:2166468","revisionNum":1,"uid":2166468,"depth":10,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"AcceptedSolutionMessage:message:1340012"},"conversation":{"__ref":"Conversation:conversation:1307297"},"subject":"Re: How do I find multiple search terms in Excel?","moderationData":{"__ref":"ModerationData:moderation_data:2166468"},"body":"
JKPieterse How do I add that in Worksheet And This picture is an example of what I want
","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"115","kudosSumWeight":0,"postTime":"2021-02-25T10:36:19.140-08:00","lastPublishTime":"2021-02-25T10:36:19.140-08:00","metrics":{"__typename":"MessageMetrics","views":235599},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:1307297/message:2166468","repliesCount":0,"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[{"__typename":"AttachmentEdge","cursor":"MjUuM3wyLjF8b3w1fF9OVl98MQ","node":{"__ref":"Attachment:{\"id\":\"attachment:message2166468AttachmentNumber1\",\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/90898/1/2021-02-25%2020_27_08-Book1%20-%20Excel.png\"}"}}],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"User:user:937150":{"__typename":"User","id":"user:937150","uid":937150,"login":"elmajen","biography":null,"registrationData":{"__typename":"RegistrationData","status":null,"registrationTime":"2021-01-21T00:11:50.731-08:00"},"deleted":false,"email":"","avatar":{"__typename":"UserAvatar","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/m_assets/avatars/default/avatar-9.svg?time=0"},"rank":{"__ref":"Rank:rank:37"},"entityType":"USER","eventPath":"community:gxcuf89792/user:937150"},"ForumReplyMessage:message:2081641":{"__typename":"ForumReplyMessage","uid":2081641,"id":"message:2081641","revisionNum":1,"author":{"__ref":"User:user:22322"},"readOnly":false,"depth":11,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:2081412"},"conversation":{"__ref":"Conversation:conversation:1307297"},"subject":"Re: How do I find multiple search terms in Excel?","moderationData":{"__ref":"ModerationData:moderation_data:2081641"},"body":"
elmajen How to do that depends on how your data is organised. If it is all in one column you may consider using advanced filter to do this.
","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"146","kudosSumWeight":0,"postTime":"2021-01-21T01:44:17.127-08:00","lastPublishTime":"2021-01-21T01:44:17.127-08:00","metrics":{"__typename":"MessageMetrics","views":241427},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:1307297/message:2081641","repliesCount":1,"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"ModerationData:moderation_data:2093118":{"__typename":"ModerationData","id":"moderation_data:2093118","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ForumReplyMessage:message:2093118":{"__typename":"ForumReplyMessage","author":{"__ref":"User:user:937150"},"id":"message:2093118","revisionNum":1,"uid":2093118,"depth":12,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:2081641"},"conversation":{"__ref":"Conversation:conversation:1307297"},"subject":"Re: How do I find multiple search terms in Excel?","moderationData":{"__ref":"ModerationData:moderation_data:2093118"},"body":"
thanks , I have had a look into advanced filter and it could be useful but I kind of need to know the location of the row item which contains the word combination, rather than just have the word returned as it is part of a bigger data set. I have taken out the two columns of narrative text which I need searched from the main data set by using a pivot table where they are displayed in one column. I'm quite confident I can find the original line items again so currently the data I need searched is all in one column but across ~95000 rows. Can you help? thanks
","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"213","kudosSumWeight":0,"postTime":"2021-01-25T00:46:41.537-08:00","lastPublishTime":"2021-01-25T00:46:41.537-08:00","metrics":{"__typename":"MessageMetrics","views":241087},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:1307297/message:2093118","repliesCount":0,"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"ForumReplyMessage:message:2081412":{"__typename":"ForumReplyMessage","uid":2081412,"id":"message:2081412","revisionNum":1,"author":{"__ref":"User:user:937150"},"readOnly":false,"depth":10,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"AcceptedSolutionMessage:message:1340012"},"conversation":{"__ref":"Conversation:conversation:1307297"},"subject":"Re: How do I find multiple search terms in Excel?","moderationData":{"__ref":"ModerationData:moderation_data:2081412"},"body":"
JKPieterse this was reall useful, thanks , is there any way you can addapt the code to include 'AND'/ 'OR' searh functions.
I would like to perform a word search like this;
financial inclusion OR payments, OR savings, OR credit, OR lending, OR loans, microloans, OR microfinance, OR insurance, OR mobile money
AND
Women, OR gender, OR female, equality, OR cooperative, OR garment workers, OR microentrepreneur, empowerment, OR VSLA, OR marginalised
Thanks
E
","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"218","kudosSumWeight":0,"postTime":"2021-01-21T00:13:26.444-08:00","lastPublishTime":"2021-01-21T00:13:26.444-08:00","metrics":{"__typename":"MessageMetrics","views":241405},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:1307297/message:2081412","repliesCount":2,"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"ModerationData:moderation_data:2081641":{"__typename":"ModerationData","id":"moderation_data:2081641","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ModerationData:moderation_data:2081412":{"__typename":"ModerationData","id":"moderation_data:2081412","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ForumReplyMessage:message:1346525":{"__typename":"ForumReplyMessage","uid":1346525,"id":"message:1346525","revisionNum":1,"author":{"__ref":"User:user:22322"},"readOnly":false,"depth":11,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:1346082"},"conversation":{"__ref":"Conversation:conversation:1307297"},"subject":"Re: How do I find multiple search terms in Excel?","moderationData":{"__ref":"ModerationData:moderation_data:1346525"},"body":"Copy the code from my post, open the Excel file, Alt+F11 to go to the VBA editor. Find a module in the list of modules in the small tree on the left that contains the \"old\" code and replace all of it with the bit you copied.","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"203","kudosSumWeight":1,"postTime":"2020-04-29T01:29:44.475-07:00","lastPublishTime":"2020-04-29T01:29:44.475-07:00","metrics":{"__typename":"MessageMetrics","views":244011},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:1307297/message:1346525","repliesCount":1,"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"ModerationData:moderation_data:1348013":{"__typename":"ModerationData","id":"moderation_data:1348013","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ForumReplyMessage:message:1348013":{"__typename":"ForumReplyMessage","author":{"__ref":"User:user:311343"},"id":"message:1348013","revisionNum":2,"uid":1348013,"depth":12,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"ForumReplyMessage:message:1346525"},"conversation":{"__ref":"Conversation:conversation:1307297"},"subject":"Re: How do I find multiple search terms in Excel?","moderationData":{"__ref":"ModerationData:moderation_data:1348013"},"body":"
JKPieterse Thanks. This seems to be working well. Great for an attorney conflicts of interest checker.
","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"121","kudosSumWeight":0,"postTime":"2020-04-29T13:23:33.561-07:00","lastPublishTime":"2020-04-29T13:26:22.182-07:00","metrics":{"__typename":"MessageMetrics","views":243991},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:1307297/message:1348013","repliesCount":0,"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"ForumReplyMessage:message:1346082":{"__typename":"ForumReplyMessage","uid":1346082,"id":"message:1346082","revisionNum":1,"author":{"__ref":"User:user:311343"},"readOnly":false,"depth":10,"hasGivenKudo":false,"subscribed":false,"board":{"__ref":"Forum:board:ExcelGeneral"},"parent":{"__ref":"AcceptedSolutionMessage:message:1340012"},"conversation":{"__ref":"Conversation:conversation:1307297"},"subject":"Re: How do I find multiple search terms in Excel?","moderationData":{"__ref":"ModerationData:moderation_data:1346082"},"body":"
JKPieterse How do I update the spreadsheet to include the new code?
","body@stripHtml({\"removeProcessingText\":false,\"removeSpoilerMarkup\":false,\"removeTocMarkup\":false,\"truncateLength\":200})@stringLength":"74","kudosSumWeight":0,"postTime":"2020-04-28T18:15:06.898-07:00","lastPublishTime":"2020-04-28T18:15:06.898-07:00","metrics":{"__typename":"MessageMetrics","views":244012},"visibilityScope":"PUBLIC","placeholder":false,"originalMessageForPlaceholder":null,"isEscalated":null,"solution":false,"entityType":"FORUM_REPLY","eventPath":"category:microsoft365/category:products-services/category:communities/community:gxcuf89792board:ExcelGeneral/message:1307297/message:1346082","repliesCount":2,"customFields":[],"attachments":{"__typename":"AttachmentConnection","edges":[],"pageInfo":{"__typename":"PageInfo","hasNextPage":false,"endCursor":null,"hasPreviousPage":false,"startCursor":null}}},"ModerationData:moderation_data:1346525":{"__typename":"ModerationData","id":"moderation_data:1346525","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"ModerationData:moderation_data:1346082":{"__typename":"ModerationData","id":"moderation_data:1346082","status":"APPROVED","rejectReason":null,"isReportedAbuse":false,"rejectUser":null,"rejectTime":null,"rejectActorType":null},"CachedAsset:text:en_US-components/attachments/AttachmentView/AttachmentViewChip-1745505307000":{"__typename":"CachedAsset","id":"text:en_US-components/attachments/AttachmentView/AttachmentViewChip-1745505307000","value":{"errorTitle":"Failed!","previewFile":"Preview File","downloadFile":"Download File {name}","removeFile":"Remove File {name}","errorBadExtension":"This file does not have a valid extension. \"{extensions}\" are the valid extensions.","errorFileEmpty":"This file is empty or does not exist.","errorTooLarge":"The maximum file size is: {maxFileSize}.","errorTooMany":"Too many attachments. The maximum number of attachments per message is: {maxAttachmentCount, number, integer}.","errorDuplicate":"This file is already attached."},"localOverride":false},"Attachment:{\"id\":\"attachment:message2166468AttachmentNumber1\",\"url\":\"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/90898/1/2021-02-25%2020_27_08-Book1%20-%20Excel.png\"}":{"__typename":"Attachment","id":"attachment:message2166468AttachmentNumber1","filename":"2021-02-25 20_27_08-Book1 - Excel.png","filesize":48457,"contentType":"image/png","url":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/attachments/gxcuf89792/ExcelGeneral/90898/1/2021-02-25%2020_27_08-Book1%20-%20Excel.png"}}}},"page":"/forums/ForumMessagePage/ForumMessagePage","query":{"boardId":"excelgeneral","messageSubject":"how-do-i-find-multiple-search-terms-in-excel","messageId":"1307297","replyId":"1340012"},"buildId":"-gVUpXaWnPcjlrLJZ92B7","runtimeConfig":{"buildInformationVisible":false,"logLevelApp":"info","logLevelMetrics":"info","openTelemetryClientEnabled":false,"openTelemetryConfigName":"o365","openTelemetryServiceVersion":"25.3.0","openTelemetryUniverse":"prod","openTelemetryCollector":"http://localhost:4318","openTelemetryRouteChangeAllowedTime":"5000","apolloDevToolsEnabled":false,"inboxMuteWipFeatureEnabled":false},"isFallback":false,"isExperimentalCompile":false,"dynamicIds":["./components/seo/QAPageSchema/QAPageSchema.tsx","./components/community/Navbar/NavbarWidget.tsx","./components/community/Breadcrumb/BreadcrumbWidget.tsx","./components/customComponent/CustomComponent/CustomComponent.tsx","./components/messages/TopicWithThreadedReplyListWidget/TopicWithThreadedReplyListWidget.tsx","./components/messages/MessageView/MessageViewStandard/MessageViewStandard.tsx","./components/external/components/ExternalComponent.tsx","./components/customComponent/CustomComponentContent/TemplateContent.tsx","../shared/client/components/common/List/UnwrappedList/UnwrappedList.tsx","./components/tags/TagView/TagView.tsx","./components/tags/TagView/TagViewChip/TagViewChip.tsx","../shared/client/components/common/List/UnstyledList/UnstyledList.tsx","./components/messages/MessageView/MessageView.tsx","./components/attachments/AttachmentView/AttachmentView.tsx","./components/attachments/AttachmentView/AttachmentViewChip/AttachmentViewChip.tsx"],"appGip":true,"scriptLoader":[{"id":"analytics","src":"https://techcommunity.microsoft.com/t5/s/gxcuf89792/pagescripts/1730819800000/analytics.js?page.id=ForumReplyPage&entity.id=board%3Aexcelgeneral&entity.id=message%3A1340012","strategy":"afterInteractive"}]}