Use Of Wildcards & Exceptions In A Lookup
Dec 15, 2009
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.
View 9 Replies
ADVERTISEMENT
Apr 7, 2009
I'm currently using a lookup table to determine programs to run on a machine, however I have 3 exceptions to this and can not get my code to work.
Currently the code looks at column D for the type, then column F for the thickness of material. It then calculates the etch time required using the rate (all shown as 1 currently) on the cal_sheet and then rounds this up to a whole number. This is then compared to a second table where the program details are listed against etch times.
View 7 Replies
View Related
Dec 4, 2007
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 Related
Feb 5, 2014
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
View 1 Replies
View Related
Mar 8, 2013
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]....
View 4 Replies
View Related
Feb 28, 2014
To establish the order for on duty personnel every month, I need to find a proper formula.
I have attached the workbook.
So, when I pick out a month from a drop list in cell A1, in column B returns (based on a formula) net work days of that month.
In range H:T, I have a table with the personnel names (in the header) and the individual holidays.
What I want is to have a formula in column C, that returns the name of the first available person (not on holiday in that day), in exactly the same order as it is in the table's header.
Attached File : ON DUTY.xlsm‎
View 5 Replies
View Related
Jul 23, 2009
I have a macro that loops through a column of text and removes all text after a hyphen. Example: AU9929-PK becomes AU9929. I need to set up some exceptions that when it processes certain text it does not truncate it. Example: AU9929-ASST stays as AU9929-ASST. So far there are twelve exceptions but this could increase.
Note: The list which includes shop number, description, sku, sku description, quantity, unit price and more is sorted in a particular order as other operations are performed on it that requires it be in a specific order.
View 4 Replies
View Related
Jan 20, 2010
I am using Excel 2007. I have a list of 100 names all ranked from 1-100. Is there a way to create a macro that can filter out the top 20, a specific name and also any names with the cell colours blue and yellow?
At the moment using an advanced filter based on criteria I can filter out the top 20 and the specific name I want but can't work out how to leave the coloured rows in as well.
View 3 Replies
View Related
Aug 22, 2007
I need to create a custom sort which will cause entries "TBD" and blank to appear at the bottom of my spreadsheet. Any other value will be sorted alphabetically. I'm not sure how to create my sort list to do this. I tried "*, TBD " but this did not work.
View 2 Replies
View Related
Feb 12, 2014
I have a workbook with timesheet records for a list of resources from multiple locations. These resources have logged time against the projects which is captured on a monthly basis. These time records are against holidays declared by the company. I would like to see if there are any of them who have logged time incorrectly on a holiday.?
View 4 Replies
View Related
Aug 11, 2009
When using conditional formatting, I have it set to the following:
C2=60 (Reference Cell)
C3=5 (Reference Cell)
Conditional Format Settings
If C3 >= C2/12 then pattern set to Green
If C3 < C2/12 then pattern set to Red
Here is the problem - when you have a value in C3, everything is fine, it's either green or red. If C3 is blank, it defaults to green because the conditional format is true.
Is there anyway to add something in there to have no color when C3 is not populated?
View 11 Replies
View Related
Oct 2, 2008
List File Name with multiple exceptions. I have this
View 2 Replies
View Related
Dec 26, 2008
I am having a problem combining two databases. I have database E and F. I need to take any price that is $0.00 from Database E and replace it with the data from database F. The catch is that there isn’t always a price to replace it with. Also, there are parts in database E that do not appear at all in database F. Below is an example (there are about 20,000 lines of data total)
In the examples above I have placed both databases together. Column four has the database designation. As you can see the 1748 Hose reducer has a price for F but none for E.
Basically I need some formula like the following:
If (part number xxx) and (part number XXX) from column 1 are the same, replace the price data from E with the price data from F but only if E = $0.00
Is this possible?
View 9 Replies
View Related
Oct 11, 2006
Basically I have the code to Delete All named ranges in active workbook, but I need it to skip over two named ranges called Categories and Length. Is there a way to adjust this to delete all named ranges in active workbook except a named range Categories and another called Length
Dim rName As Name
For Each rName In ActiveWorkbook.Names
rName.Delete
Next rName
View 2 Replies
View Related
Aug 14, 2007
I am calculating blood sugars for my daughter and putting them in a spreadsheet. The formula is simple:
Blood sugar - target blood sugar / 20 which yields the amount of insulin that she needs to take. I want this cell to be blank unless I enter a value in the cell above it.
Next, I take the value from that formula and add it to the amount of insulin that she takes for eating.
The value in the top formula can be 0 or less, but, if the value in the bottom one is less than 0, I want the cell to be blank.
View 9 Replies
View Related
Oct 26, 2007
I'm having problem with the ISBLANK function. I have attached my workbooks if someone would care to look at them. My macro basically loads two lists from other workbooks (old & new (attached)). It then finds out which entries are unique to each list, and places them in the EXCEPTIONS sheet.
Column C in these sheets should say TRUE or FALSE as to whether the corresponding cells in Column B are blank but it does not work. Book1.xls contains my macro.
Old.xls and New.xls will need to be selected when prompted.
View 3 Replies
View Related
Nov 24, 2006
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 *)?
View 9 Replies
View Related
Mar 5, 2007
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.
View 13 Replies
View Related
Jun 5, 2009
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.
View 9 Replies
View Related
Aug 16, 2007
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?
View 13 Replies
View Related
Jul 8, 2009
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.
View 2 Replies
View Related
Jul 20, 2009
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
View 10 Replies
View Related
Jul 22, 2013
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 Related
Jul 10, 2007
I 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,"***************","**/**-**-***-****/*")
View 10 Replies
View Related
Jun 23, 2009
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.
View 5 Replies
View Related
Jul 10, 2009
=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.
View 2 Replies
View Related
Jan 17, 2013
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.
View 9 Replies
View Related
Sep 11, 2013
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 Related
Jan 2, 2014
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
[Code] ........
View 3 Replies
View Related
Jan 17, 2008
I 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.
View 9 Replies
View Related
