I need to match up two columns of data. I need to match the first column with text in the second column that is "like" the first. Example..
Column A
111
Column B
000111222
Basically I need to search on only part of the text in column B. So I would need to wild card both sides of the text like "*111*".
I have a list of rules on how to group account numbers. An example is below. I have a very long list of full account numbers (no wildcards). There are roughly 75 rules on how to group the over 40,000 accounts. I want to use the vlookup to determine what group each full account number (always 11 characters) would belong to? It seems that the vlookup isn't treating the ?'s as wildcards. I can use the full account number rules with the wildcards in the cell in the sumif function though.
So ultimately I would like to have my list of account numbers like 40000000000 and 40000000001 know that they belong in group 1 based on the rule table below.
Full Account Number
Account
Subaccount
Group
[code]....
I'm using the sum if function but can't get the correct answer because the text that I'm searching for includes * which I assume is the wildcard symbol. I'm looking to sum on the text integrated projects* but it's returning a value which also includes integrated projects* VAT.
I don't want to have to change the text (e.g. by using find and replace). Is there any way excel can incluce just the exact text I need (including *)?
I need to write a macro whereby it queries a cell and if there is any value (it will be either blank or contain characters) in the cell a new row is to be inserted directly above the cell.
on one page I have a list of dates in one column and a list of numbers in another column. I want to look up January say in the dates column and sum the relevant numbers in the other column, (all the dates start with 01, i.e. the first day of the month), so I tried:
=SUMIF(Sheet2!A6:A117,"01/01/????",Sheet2!I6:I117)
If I put in the exact date, i.e. replace ???? with 2008, then it works fine, just returns the number for that one date, but with the wildcards, I always get zero.
I'm doing a search simular to this
=VLOOKUP(D*,Sheet2!A*:B*,2,FALSE)
and I want it replaced with this
=VLOOKUP(D3,Sheet2!A1:B42,2,FALSE)
HOWEVER when I do the find & replace I don't want it to change whatever the current value is to D3 I want it to stay as whatever it was already....so how do I exempt the "D" value from the search?
I'm working with several columns of data that represent the types of employees we have at my organization. I'm trying to count the number of each type by status, as well as determine the salary payments to each type.
My problem is that we have titles like "PAA I" "PAA II" and "PAA III" and I've not been able to quite figure out the right formula to deal with this problem.
how to do a find and replace on the following
090226000/Jones/123 Main Street
All data has numerical 9 places first field
I need to eliminate the /Jones/123 Main Street
so that I just have 090226000
I thought * would work but it's not returning the
correct output
I have an excel file where the user has to enter his user name and password. But the password that is entered into the cell should appear as Wildcards(*) to everyone (Like our Windows login). Is there any way to do it.
View 1 Replies View RelatedI have numeric values in approx 1000 cell entries that I need to edit:
example cell C1 contains the value 00100300308W400
I'm trying to edit this entry to show 00/10-03-003-08W4/0.
I've tried using wildcards with the SUBSTITUTE function:
=SUBSTITUTE(C1,"***************","**/**-**-***-****/*")
I've got a column with client codes (col. B) and another one with material codes (col. C). Both columns mix numbers and text. First condition: If cells in "B" column equal a specific code ("DA2")
Second condition: AND If the cells in "C" column start with a certain pattern ("30") followed by some other pattern (four random digits, a dash, and two or three more random digits). THEN a new cell should say "T1", Else it should just copy another cell.
=SUMPRODUCT(--('Trial Balance'!A1:A100="6120"),--(ISNUMBER(MATCH('Trial Balance'!B1:B100,{"00.00.01","00.01.01"},0))),'Trial Balance'!G1:G100)
Is there a way to use this formula, but instead of looking in Column B, and having to list 00.00.01, 00.01.01, that it only looks at the last two digits of the ##.##.##.
Here is the issue. Each month, the numbers that make up what I need to add together will change. However, all I am really doing, is combining any accounts that have the same sub account (Column B) ending in the same digits of either 00, 01, 02, or 08. So I could have 01.01.01, 01.00.01, 01.02.01, etc. and I am just focusing on the .01 on the end. I would like to say, look at all the sub accounts in B, and only add (G:G) on those that the last two digits equal .01.
I'm trying to use wildcards in the Search formula and it isn't working the way I thought it would. I'm trying to use the following two strings:
????1234
compared to
1234????
Shouldn't search find these two strings the same since given the wildcards? Or am I missing something? If I use the above, I receive a VALUE error rather than a match.
In cells G2 to G57 I have the months from Jan-09 to Aug-13. In H3 to H57 I have the monthly data. In cell k2 I am trying to average all of the December data without individually clicking each cell (Ex: Dec-09, Dec-10, Dec-11, Dec-12). I was thinking of doing Averageif formula like =AVERAGEIF(G2:G57,"Dec*",H2:H57) but I get the dreaded #DIV error.
View 1 Replies View RelatedI have a problem with the code below. I've borrowed parts of it from various online sources and attempted to make it my own. I need it to go through a column of data that is imported into column A. The unique search criteria are rows that goes sequentially (i.e. 1, 2, 3). The format for the search is (#. text). I need to copy those rows to sheet 2. The problem with the code is that it only copies the first occurrence of the sequence (the row with 1.) to sheet 2 and stops.
Here's an example of the data:
Kalé
1. Kalé
$$ Japanese, Sushi Bars
Shigezo
2. Shigezo
Southwest Portland, Downtown
Code:
Sub Copy_FSE_yelp()
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 'Finds last row
For i = 1 To LastRow 'creates loop for column
If Cells(i, 1) Like "*" & "." & "*" Then
Range(Cells(i - 1, 1), Cells(i - 1, 1)).Select
Selection.Copy
[Code] ........
When I use a lookup to look for "*"&A1&"*" where A1 contains "AB" I get a value not available error despite the fact that in the postcode table there are loads of postcodes beginning with "AB...". Is it the multiple entries of "AB" in the lookup table that will be messing this search up?
View 9 Replies View RelatedI have about 80 cells in a row with a varient of this in it:
=VLOOKUP($C2,LookupData!$B$494:$G$699,6,FALSE)
The strings on the LookupData sheet change to look for different things, but
I want to modify to this:
=(.01*VLOOKUP($C2,LookupData!$B$494:$G$699,6,FALSE))*$F2
I can locate all the cells in question using EDIT>REPLACE,
FindWhat=VLOOKUP(*,*,*,*)
But when I put this
=(0.01*VLOOKUP(*,*,*,*))*$F2
in the ReplaceWith box, and try to replace, I get an error message saying the formula contains an error.
Once I figure this out, I'm filling down about 60000 rows, so I'd like to get it right the first time.
I have a worksheet that conatins multiple columns that are populated from a sql query.One column is a parts list.This has 1705 rows.
I have another worksheet that contains a list of parts in one column.This list is varying in row length.
I want to be able to produce a list that only contains matching data , including () around parts and if not too difficult an indication of data that is not in first list but is in second list.
When I do an advanced filter using the second list as the criteria i am not recieving all of the data from the filter , ie in the first list there is parts in (1234XYZ ) but in the second list the part is 1234XYZ , this part is not being resulted in the advanced filter unless I put brackets around the part in the second list . There is no way of knowing which part is in brackets in the first list.
I matching up some ip numbers against some ip ranges and I'm finding I can only use the wildcards in the first argument and not the second array argument. Is that the case? Here's my files:
IP_List
141.188.146.000 =match(A1,IP_Range!A:A,0) Doesnt work
156.140.216.202
IP_Range
141.188.???.??? =match(A1,IP_List!A:A,0) works
I really need the first case to work (looking up an ip in the ip range). Is there a way to do this?
I have a workbook (Data) that I am entering "job title" into column G. Based on key words in the job title I am then manually entering "level" into Column V.
I have created a worksheet (Level Matrix) that has the following
Column A = Job title
Column B - Level
Column C = Exceptions
What I have in column A are the key words - as an example *Director* (wildcard Director Wildcard); in column B is the word Director
What I want the system to do is look for the word DIRECTOR anyplace in the job title entered into Column G of the data worksheet (hence why I have the title between wildcards. IF it finds it then I want to add the level automatically (from Column B of the Level Matrix worksheet).
However if the compare finds any of the words in the title that match any of the words in the Exceptions Column (Column C of the Level Matrix) then I DO NOT WANT To autoload the Director level- it would load DNA. As an example- an Art Director would not load "Director" becasue the word "art" is one of the words in the exceptions column of the Level Matrix.
I have discovered that I can use a wildcard in a function such as =COUNTIF(B3:H14,"L*"). This will count all cells in the range that start with "L". However, I can't get it to work with conditional formatting. Is that correct? I am using Office 2000.
View 9 Replies View RelatedI know that I can use Ctl H to Find and replace say all APTs by typing APT*.
Here's a small sample of my data. How do I get rid of the letters at the end of the street numbers so that I can sort properly?
KOGASAKA561 KAWAINUI ST
MORIMOTO570 KAWAINUI ST
KERNS587 KAWAINUI ST
TOI587 KAWAINUI ST
UNO606 KAWAINUI ST
CAVACO611 KAWAINUI ST
TAKARA315A KAWAINUI ST
NAKAMOTO350A KAWAINUI ST
NISSLE419B KAWAINUI ST
MONTANA419C KAWAINUI ST
LIU428A KAWAINUI ST
HABERSTROH533A KAWAINUI ST
IIDA545A KAWAINUI ST
MEYER328 KAWAINUI ST
AKITA331 KAWAINUI ST
I have two questions. Below is a code snippet that demonstrates what I'm trying to achieve.
Const D_WIL_SearchString As String = "[HTML ON]*<a title=""*"" target=""blank""href=""*"">*</a>*[HTML OFF]"
If D_Info(i).WIL Like D_WIL_SearchString ThenDo AElseDo BEnd If
The double quotes ("") are supposed to represent a single double quote within C_WIL_SearchString.
The asterisks are supposed to represent any characters of any length.
All other characters (except for the initial double quotes (")) should be present within the string D_Info(i).WIL
I'm having issues ensuring that this code works correctly though, as it does not appear to search for the string that I'd like it to.
As a second question, I would like to know if there is a way of ensuring the asterisks (or another similar character) only allow whitespace? (i.e. spaces and new lines)
I have a list of bank transactions in a spreadsheet
for example....
1/7/09 Sainsburys Petrol 34856385
4/7/09 Paypal 92017465
6/7/09 Sainsburys S/Market 38264059
8/7/09 Halfords 93862830
11/7/09 Sainsburys S/Market 34957372
13/7/09 Paypal 92037454
14/7/09 Barclays BCC 173256
and wish to use formulae similar to MAXIF and MINIF to find the last date and the first date of Paypal transactions (for example) if I have just the word "Paypal" in cell D1
I've tried
=SUMPRODUCT(MAX(($B$1:$B$7=$D$1)*$A$1:$A$7))
but obviously this is looking for exact match of "Paypal"
I have successfully used a wildcard in SUMIF functions elsewhere...
eg, $D$1 & "*"
but for some reason
=SUMPRODUCT(MAX(($B$1:$B$7=$D$1 & "*")*$A$1:$A$7))
does not work
Does anyone have any suggestions?
I am trying to count the number of time the 15th and the 28th of the month show up in Column A. Column A is in date format but not all cells have a date. In this example I am only looking for the 15th but in the end I want the combined count of both.
This formula gives me a count of 1
=(COUNTIF(TimeSheets!A1:A119,"3/15/2012"))
These return 0
=(COUNTIF(TimeSheets!A1:A119,"*15*"))
=(COUNTIF(TimeSheets!A1:A119,"*/15/*"))
Or any other combination of wildcards with * or ?
Maybe there is something other than COUNTIF I can use.
DateThursday, March 01, 2012Friday, March 02, 2012Saturday, March 03, 2012Sunday, March 04, 2012Monday, March 05, 2012Tuesday, March 06, 2012Wednesday, March 07, 2012Thursday, March 08, 2012Friday, March 09, 2012Saturday, March 10, 2012Sunday, March 11, 2012Monday, March 12, 2012Tuesday, March 13, 2012Wednesday, March 14, 2012Thursday, March 15, 2012 Total Hours WorkedHours In This Pay Period
I am trying to creat a woorksheet that will calculate the number of time a test question was wrong. I am trying to acount for the various methods the data will be entered, with spaces or commas ect... this is the current fumula that I am using but it is not performing the desired effect. The problem is it is ramdomly counting 1,2,3,4,5,6,7,8,9 when the double digit numbers are entered. is there a better way to achieve this than the current formula bellow.
starting from 1
=COUNTIF($C15:$C66,"*"&1&"*")+COUNTIF($C15:$C66,"1")
ending at 50
=COUNTIF($C15:$C66,"*"&50&"*")+COUNTIF($C15:$C66,"50")
-I have 3 relevant columns, A, B and C with 999 rows
-Column A is a status indicator "Yes" or "No" are the only options
-Coumn B a list of vendor names, which requires the wildcard example:"*Verizon*"
-Column C is the data range that needs to be summed
Right now i have this:
=SUMPRODUCT(--(A2:A999="No"),--(B2:B999="*VERIZON*"),C2:C999)
But, it seems sumproduct does not allow wildcard matches within it's "--" arrays. I am willing to try something else if possible, but I lack the excel knowledge to answer this myself.
Is it possible to find/open a workbook if we know its name partially.I mean just like that.
Sub Open_WB
Dim wb As workbook
Set wb = workbooks.open("D:*xyz*.xls")
End Sub
I have a 14 000-item list of product codes that are generated from different attributes. What I am trying to do, is to create a cover sheet where the user can select attributes from dropdown lists and get the first matching product code. I have tried using index/match, but I am struggling with wildcard lookup.
Example of generated product code:Cell A1:
ABX**J
Here, AB is the product group, X is the pressure class, ** is material (which is, for the example, unknown), and J is the end connection. What I now want to do, is to look in the long list of codes and find the first match:
Cells A2:A5:AAY02J
ABY03F
ABX01J
I am trying to set a file name as a string to use it as an attachment. The user inputs a number into a textbox. That number is used to locate the file in location. But the file name is different with each file. So im trying to do something like
"C:Desktop*TextBox1*.docx"
Whereas the actual file name is "C:DesktopMCJC RFI 1 MISSING ROOM NUMBERS.docx"
The location "C:Desktop" will always remain the same as will ".docx". I have everything else working just fine.