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:
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 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.
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.
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.
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.
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.
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.
I 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
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?
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.
I 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 am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
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 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 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:
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.
