Search On Multiple Entries Using Wildcard
Jul 29, 2014
I'm trying to perform a search of over 15,000 entries using a wildcard. Sheet1 is a list of streets in the county where I live, and I would like to be able to type a part of the name that will return every instance of that name appearing.
I found a code which i have tried to adapt, but it is not working.
[Code] ....
The item being searched will be entered into TextBox1, and I'm guessing that the error I am receiving is in the .AutoFilter Field:=1, Criteria1:=TextBox1"*" line of code. This is the original sample I found onlne:
[Code] .....
where they were only looking for the letters "GB".
View 14 Replies
ADVERTISEMENT
Dec 29, 2011
I have a spreadsheet where there are lines of different Wage Types but only certain combinations are needed. For example I have Pay Area 1234 which needs all wage types except those beginning with a 9.
To do this I have set up Sheet1 to look like this:
12341
12342
12343
12344
12345
12346
12347
12348
and not 12349 as I do not require this type.
The problem here is I need to add a wildcard to the end of these so it finds anything that starts with 12341 etc.
Sheet1 contains the personal details of employees and I have concatenated the Payroll Area and Wage type in column D.
Code:
Sub deleterows()
Application.ScreenUpdating = False
Set MySheet = Sheets("data")
LR = MySheet.Cells(Rows.Count, "D").End(xlUp).Row
For i = LR To 2 Step -1
[Code]...
View 9 Replies
View Related
Jan 21, 2009
I have numerous formulas that use a macro with an indirect cell reference, for example:
INDIRECT(macro(A21))
I am moving these formulas to a different workbook and I need them to still refer to items in the old sheet. I accomplish this by using this formula:
INDIRECT(CONCATENATE("OtherSheet.xls!",macro(A21)))
This works. However, I need to do a global search and replace for all of these formulas without deleting my indirect cell reference. I could replace
macro
with
CONCATENATE("OtherSheet.xls!",macro
but that would leave me short a ).
Is there a way to use a wildcard to accomplish this? It does not seem that replace
macro(*)
with
CONCATENATE("OtherSheet.xls!",macro(*))
View 9 Replies
View Related
Oct 28, 2008
picture this.
Cell A1 = xyz3
Cell B1 = 5
Cell C1 = xyz3-hgfe-56f
I need to do a vlookup on C1 in range A1:B1 that will return B1 if C1 contains the string in A1. I've tried using the TRUE variable in the vlookup formula which does not require an exact match. This works for about 80% of my data but is not 100% accurate. Of course I've got a long list of data ... not just the 3 cells.
View 4 Replies
View Related
Nov 20, 2006
I need to get rid of the questions marks in the text on my sheet, but am unable to get my VBA code to search for an actual question mark and not the wildcard. I'm using the replace function as follows:
wsReport. Cells = wsReport.Cells.Replace("?", "")
1. What's the trick to searching for wildcard characters?
2. Is there a slicker way to do this?
View 3 Replies
View Related
Dec 10, 2013
I have a list of titles that I would like to see if these titles appears in another cell.
Here is the example:
A1
Manager
Assistant Manager
Manager, II
VP
Vice President, Sales
Assistant
Associate
I would like to see if it appear in this cell, regardless where it is the exact text match
B1
Manager, Assistant, VP, Sales
So the result will be like: Screenshot_1.png
View 4 Replies
View Related
Apr 30, 2009
i am having some difficulty modifing some existing code which is this: It will count all entries from G14 to G217 if *Ost* is in E14:E217
View 3 Replies
View Related
Jul 10, 2012
I have a sheet with Names in it. I need to look those names up and find which org they belong to. The problem is the the names have many characters after them, and many names belong to a few orgs, with a the rest belonging to many orgs. (See below)
I have been trying to get a Vlookup to work, but since it can't go left, it won't look up the orgs that don't own at least three names. And I can't get the search to work on the names.
Names:
WSGHG1-8410YFH
WSSMXG-8401B8T
NBSMXG-0310V6R
NBXG-0310V6V
[code]....
View 4 Replies
View Related
Dec 19, 2012
I would like to search for a certain pattern that appears in a cell. For example, I have certain cells that begin with a 7 digit project code (Ex: "1234567 - Project Red"). I was planning to use an IF inside a FOR loop and then do an action when I find the cell with the pattern. I thought I remember it being something like Format(########*) so that I have the wildcard on the end since there is more than just the numbers. how to make other patterns work such as Number-Letter-Number or Capital-Lowercase-Capital
View 3 Replies
View Related
Dec 26, 2012
I am trying to run a routine in VBA that will Search for a Part-No. that normally contains asterisks. It is giving me bad positive Search results due to the coding looking at the (*) asterisk's as a wildcard character.
The following Part-No.'s are located in cells (C11:C16):
9L3Z*17757*BCP
6C3Z*16138*AACP
AE5Z*16138*B
AE5Z*17E811*CF
AE5Z*17E811*F
AE5Z*17E810*F
Part-No. to be Searched for:
AE5Z*17E811*F
When the below code is run, the incorrect Part-No. 'AE5Z*17E811*CF' is found in cell (C14) rather than the correct Part-No. found in Cell (C15).
Code:
'The following 2 lines are defaulted for ease of coding
pNumb = "AE5Z*17E811*F"
Range("C11", "C16").Select
[Code] ......
View 5 Replies
View Related
Jul 29, 2014
I have a formula in column J that is the following:
=IF(I541="",IF(ISNUMBER(SEARCH(LEFT(G541,4)&"*"&RIGHT(G541,4),H541)),G541,""),"")
See screenshot:
I need to replace "G542" in the formula above. For example, in cell J541 shown above, I need it to output "1996-1999|2000-2001" instead of "1996-2001" I need the output to include LEFT(G541,4)&"*"&RIGHT(G541,4)
View 2 Replies
View Related
Feb 27, 2007
I am looking for a way, to search a cell that has a no more than 4 words sperated by a space, and if a match display it.
I had something like this:
IF(Search=LocationList,"true","False"
So the Locationlist is a list of locations that is brought from another sheet with this:
=Addresses!A3&" "&Addresses!B3
and i need to search the cells with in LocationList (about 4000 cells) for a match... is this possbile ?
View 9 Replies
View Related
Jun 9, 2014
I am building somewhat of a rudimentary search engine that looks for a name, address or unique identifier, using an Index Match formula that searches with a wild card:
=INDEX(Name,MATCH("*"&F3&"*",Name,0))
"Name": is the defined name for column C on a separate sheet.
F3: is "search field"
The current formula (above), only returns the first match. The source data is only 927 lines so not overly large. I would like to have an additional page that will show the potential results from the wildcard search via a formula. Therefore needing results; 2, 3, 4, 5 etc. from the Index Match. I have searched high and low for solutions but I have found none in regards to retrieving multiple results from a wildcard search, only on a specific term.
View 3 Replies
View Related
May 27, 2014
I have been trying to find a formula for this but I have yet to find one that works for me.
Data;
English - United Kingdom, Czech, Spanish
UK - United Kingdom
Swedish
English - US
French
US
France
German
Portuguese
+8000 more various values
I am looking for a formula that can search for multiple values e.g. english, us, uk and united and then return the value "Yes".
I was looking for something that includes wildcard searches or contains so that it is not affected by upper or lower case but combining this with multiple searches was proving difficult.
View 7 Replies
View Related
May 30, 2014
Can I use the asterisk (*) wildcard to save the file in multiple PATH as long as it the file is in the master drive C:
Ex: the file now is in "C:Public" then can I use "C:Public*" so user can save file at their desire sub-folder such as "C:PublicHenry" as long as it has to be in drive C
View 6 Replies
View Related
Nov 24, 2008
I'm trying to write this but it returns a 0 when I know there are 3 records that match this criteria: =SUMPRODUCT(('Invoice-Detail'!J2:J50="NewJob_Post.NET")*('Invoice-Detail'!H2:H50="KY_*")). I think the problem is in the wildcard character. I don't know if I should be using COUNTIF or SUMPRODUCT or something else?
View 3 Replies
View Related
Oct 3, 2013
I have a list of entries of which some are distinct and others are similar. For example, I have 1) JohnSmith 2) JhonSmith 3)SomeoneElse. The first and second entry are similar but not equal due to a spelling mistake, the third entry is not related to the first. My question is: How can I obtain which entries belong together? The output would have to show that 1&2 are similar and that 3 is different.
View 4 Replies
View Related
Feb 8, 2014
This follows on from my previous posting [URL] ..... which produced a solution using an ActiveX Combobox that unfortunately does not work on Mac PCs!
I tried to replace the ActiveX with a Form Control Combobox but could not make it work.
So I am trying to use the alternative of "find, copy and paste" the relevant information.
As shown on the attached 140207 FINDALL test.xlsm, I need to find all records containing whatever string is entered into the "Search" cell, and copy data form three columns onto the Entry sheet.
The User will then select whichever of the entries they want to use, which will populate the relevant cells.
Problem: The following Code is not recognising any of the data in the Column being searched.
VB:
Option Explicit
Sub FINDPARTS()
Dim ws As Worksheet, i As Integer, k As Integer, z As Integer, CL, myFind, CHOICE As Range, lr As String, lrG As String,
[Code] ......
View 2 Replies
View Related
Oct 11, 2011
Im am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do
Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then
[Code]....
View 1 Replies
View Related
Nov 2, 2009
I have a workbook with many many sheets in it. The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so). If matches are found (they don't have to be exact case), then I'd like two things to happen:
1. The rows containing the matched search criteria in the first sheet are highlighted.
2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.
I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms.
View 3 Replies
View Related
Jun 17, 2014
I need to convert data from column IDS into separate rows, all other columns need to stay in tact. There are several distinct patterns for the IDS column, main identifiers are always starting with FILER or TEAL and the trailing numbers behind it have no more than 6 digits.
BEFORE MACRO
ID
AREA
TYPE
CLASS
QTY
IDS
1
COAL
TYPE9917312
CLASS881345
2
FILER756911**/**FILER123188 ^** FILER877119*118
[Code] ........
AFTER MACRO
ID
AREA
TYPE
CLASS
QTY
IDS
1
COAL
TYPE9917312
CLASS881345
2
FILER756911
[Code] ......
What the MACRO would look like? This is for Excel 2010.
View 3 Replies
View Related
Jan 15, 2013
I am looking to split multiple different entries in a single cell into multiple columns and repeat this for all rows
Example (I have the below in a single Cell as column headers)
NCM Server Mgmt VLAN Site ID
Next Line down is the data (Each row in a single cell)
Enabled 10.10.10.0 50 TEST SITE 1
Enabled 10.10.20.0 50 TEST SITE 2
Disabled 10.10.30.0 50 TEST SITE 3
How I could achieve this as I have a number of projects where this would become useful
I know you can use delimiters but with spaces between the values I just can't fathom a way forward.
View 12 Replies
View Related
Aug 21, 2009
I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.
I can get it to work to search one cell (as below):
=IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)
(In this case the respone is No Change as Cell G10 contains "Same")
However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:
=IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10)
Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.
View 9 Replies
View Related
May 4, 2014
I have column A and it has 1000 rows, every row has a number in it, from 5000 to 5200, meaning that some numbers are presented multiple times in column A.
I need to lose repetitions, so every number is in the the table only one time and then I need to convert this one long column into, for example, 9 columns, so there's no wasting of space and have only one column in every page, if printed out.
View 5 Replies
View Related
Mar 4, 2014
I need one formula to copy and paste the checkcard data changing it from this "CHECKCARD XXXXXX5623" to "CHECKCARD 5623", but I need one formula to look for multiple known checkcard numbers. I will have more then one typically in the bank download so I need to search and copy at least two known numbers as shown below.
I am providing the formula that I am using right now, "=IF(ISNUMBER(SEARCH("5623",B3)),RIGHT(B3,41),"")" but it only works for one checkcard number? Also I don't really need to capture the DEBIT or CREDIT that will always be at the end, it would be redundant. If it's easier to write the formula including that data that is no problem. What I want to copy and move into an adjacent cell is in red.
Here is the source data:
Row 2 - SONIC DRIVE IN#1531 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX5623 DEBIT
Row 3 - JASON'S DELI # 125 Q64 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX9718 DEBIT
View 4 Replies
View Related
Sep 30, 2009
I'm trying to identify duplicates with a return of "True" or "False". The attached workbook has column F for results (to read PI2 A if in column L, PI2 B if in column R or Both), but I want to query if everyone in column E is in column L or is in Column R and if so which one.
View 3 Replies
View Related
Jul 14, 2013
I have data like this
namefoodripeness
andyorangeC
andyyogurtD
andykiwiE
andystrawberyD
andyyogurtC
andybanannaA
andyorangeB
bobbanannaA
boborangeA
bobstrawberyA
bobkiwiB
bobyogurtC
bobpearC
stevekiwiC
stevebanannaC
steveorangeA
steveyogurtC
and i would like the data to look like this..............
orangeyogurtkiwistrawberyorangepear
andyCDEDC
bobACBC
steveACCA
not sure how to do it.
View 1 Replies
View Related
Feb 10, 2014
I have an excel file containing more than 70,000 lines (items and their corresponding orders) and in second file i have all the items listed. I want to find how using excel functions like vlookup i can return all the orders (from first file) against the items (in second file).
Attached a simple example of my problem. Please note that both tables are in different sheets of an file.
ITEMS
ORDERS
A
2
B
3
[Code] .......
View 7 Replies
View Related
Apr 28, 2012
I am having such a difficult time creating a macro that will reduce the 5+hours I have to spend each week manually copying & pasting all of this data. I making an IMMENSE difference in this worker bee's life!
I have a workbook with two sheets (Sheet1 & Sheet2). Sheet1 has license #'s in column A and the state that the license belongs to in column B like this:
COLUMN ACOLUMN B11111Alaska11112Alabama11113Arkansas11114Arkansas
Sheet2 has three columns. Column A has the license #'s, column B has the state that the license belongs to and Columns C shows a line-of-authority tied to that license #.
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty11111AlaskaCasualty11112AlaskaLife11112AlaskaHealth11112
AlabamaProperty11112AlabamaCasualty11113ArkansasLife11113ArkansasHealth11114
ArkansasLife11114ArkansasHealth12345ArizonaProperty
I'm trying to write a macro that will compare the license # and state in Sheet1 to the license # and state in Sheet2. If it matches, append the contents of Column C to the corresponding row in Sheet1.
Here's the thing...Sheet2 contains the entries for all licenses in the company (so this table is HUGE). And there are multiple entries for each state license # (notice how there's two entries above for AK license # 11111 - one for the Property line and one for the Casualty line.
After my macro is run, I want Sheet1 to show all the lines-of-authority on a single line. So if I ran my macro on the above example, after it's run I would have this in Sheet1:
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty Casualty11112AlabamaProperty Casualty11113
ArkansasLife Health11114ArkansasLife Health
View 5 Replies
View Related
Mar 11, 2014
I have a spreadsheet with 1,000 rows of data.
Each Cell in Column A has a different long text string.
I need to see which (if any) of 10 specific small text strings exist within each long text string. Depending on which small text string is found I want to return a 3 digit code. If no small text string is found I want to return "Not Found"
E.g.:
- Cell A2 contains "randomtext,randomtext,APPLE,randomntext"
- I want to see if Cell A2 contains any of the words APPLE, ORANGE, CARROT.
- I want to return "APP", "ORG", "CAR" or "Not Found"
Q: What is the most elegant way to accomplish this within a single formula that I could paste into each cell in Column B?
View 2 Replies
View Related
