Searching Multiple Fields
Sep 27, 2009
I am fillin out a spread sheed with a num ber of codes. I want to create a excel formula that will look at the entire column and search for each of the codes. If I have forgotten to add one of the codes into the column I need the formula to let me know.
What I was thinking might work is this...
To look for one of the codes I would do this.
=IF(MATCH("FLA",E5:E36,0)>0,TRUE,FALSE)
This works great if the Value is in there. If it is not in there I get #N/A instead of FALSE. Is there a way to get the MATCH to return another value if it is not found in the range?
Is there an easier way to look for multiple values? So far the only solution I have found is like this
=IF(LEFT(Q19,1)="D",7.5,IF(LEFT(Q19,3)="FLB",7.5,0))
For each value I need to look up I add another IF function. I have about 17 different values I need to look for. This makes for a ugly function but it seems to work.
View 9 Replies
ADVERTISEMENT
Feb 24, 2014
Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.
In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.
CURRENT SHEET
ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89PENS ABC STATIONERY CO LLC [code]....
The attached example file may be a better alternative to view this problem.
View 2 Replies
View Related
Feb 19, 2009
I am trying to set up a "diet log" for my nephew to track his eating habits for a school project. I am trying to make the "total" section of the attached spread sheet auto calculating depending on the information placed in the "breakfast / lunch / dinner / snack" catagories. I have two worksheets in the file. The first being the preferred method of a single cell and seperating the items using only a ",". The second sheet has a little bit different lay out which I don't think will work as nicely.
Honestly, I am not sure if this will even be possible, but I figured I would try here as a final resort. Currently there are just some sample foods in there as I will let him fill in the actual workbook for his use.
View 7 Replies
View Related
Jul 23, 2012
I have a spreadsheet setup like a timesheet: Employee Name, Classification, Rate, etc.
I setup the Employee Name field as a drop-down. Since there are numerous employees with different rates, I want to just select a name so that the other fields fill-in with the appropriate rates.
View 1 Replies
View Related
May 24, 2006
I have been using the VLOOKUP function to search a table of data for a specific value in order to then give me the corresponding data associated with that value. I can do this and it works!!
However, as I am searching for names some people have more than one set of data and the VLOOKUP only finds either the first set of values (if the FALSE command is used for ‘range_lookup’) or the last set of values (if the TRUE command is used for ‘range_lookup’). I have attached a small example:
How can I get all values to be displayed?
I know that this may have been explained horrendously but I have written and re-written this several times and this is the best I can get!
View 9 Replies
View Related
Sep 29, 2006
i am trying to set up an interface through Excel for a database of record also held on excel. I have very very basic VB skills and have no idea what im doing lol but what i would like to do is have a search box and button, something similar as you would find on most websites. I would also like (if possible) for it to search up to 8 workbooks to look for a name or vaule.
View 3 Replies
View Related
Aug 12, 2014
I want to search a longer text field for a shorter string that may be within it. However, I want to do this for multiple short strings.
For instance:
PhraseHidden word
Please search the text for the hidden message: Blue
The hidden word, Green, may be at any point
And the word may be different lengths like Yellow
I have a table of the hidden words (Blue, Green, Yellow, Pink)
I know I could use search to find one word, but is there a way to look for multiple words, and return the value of which one it finds? I have many hidden words (and the list is dynamic) so I don't want to just split into multiple searches.
View 5 Replies
View Related
Nov 1, 2009
I have an array of data that i need to find multiple data points in: ...
View 11 Replies
View Related
Mar 26, 2009
Fall - Columns A-G
Spring - Columns I-O
Summer - Columns Q-W
Unduplicated - Columns Y-AD
I am needing to take each student in the Unduplicated section and see which terms they attended. If they attended a specific term, I need to grab a piece of data from that term and put it in over in the undulicated area.
The only way I could think to do this is with three separate searches. This is probably grossly inefficient and so far it has not worked at all. I have posted below the code I am currently using. Again this code is not workin at all.
View 2 Replies
View Related
May 13, 2009
I need to do the following:I have multiple worksheets/ workbooks that hold user info such as address, country, first name,last name, ID,dob, gender, occupation etc
I need to make a search dialog whereby the user is allowed to do this:
1-Search by ID by selecting thru the calendar tool OR
2- search by first and last name. When he clicks search, I need the particular worksheet holding the data to pop up and show the user the data that was requested for.All data in ID column is unique...but for first n last name, i have a few people who have identical names
View 2 Replies
View Related
Feb 2, 2014
i have a spreadsheet that i use for work which has 1 page that contains all the data for the workbook. on other pages i use this data, and tables, to pull information to those pages or other pages which complete my work in a more timely fashion.
that issue i'm having is when i try to look up specific values within the data page tables and there are multiple values.
i need a way to choose different values rather than the "first" value in the instance of a vlookup which only finds the first value.
in the pictures i have picture 1 (vlookup samples) has a section where i can type in the "office" code and it will auto pull "switch name" and "switch clli" via vloookup.
in picture 2 (switches sample) i have created a table, which is on another page as stated above, but if i try to search for the example "LSGT5" it only returns a switch name value of "LFTYINXFDS0" and there is also a value of "LFYTINXFDS1".
there are many more within the table(for this office code alone) but this is just an example of how i need a way to choose the proper one or somehow have a drop down without having to create a bunch of tables for each office itself.
View 6 Replies
View Related
Mar 6, 2007
I am looking to perform a search that will return a match and place it somewhere else, and then search again until its finished the list?
View 9 Replies
View Related
Aug 27, 2009
I have two lists. One is a database (Worksheet 2) with a lot of information, and another is the list (worksheet 1) I need to fill up the data for. What I want to do is to search for the names that are in worksheet 1 in worksheet 2, and if there is a match, retrieve the missing data in those rows, and paste it back into worksheet 1. For example:
Worksheet 1: Joe
Tim 23
Matt
Worksheet 2: Alex 45
Joe 23
Mark 47
Tim 44
Matt 24
I basically want it to be able to search for the corresponding name, and then pull the number associated with it.
View 2 Replies
View Related
Dec 10, 2013
The following code works fine to determine if a particular character occurs within the selected range of cells:
Code:
Sub CheckIfCharacterIncluded()
For Each MyCell In Selection
If InStr(MyCell.Formula, "#") Then
MsgBox ("The " & "#" & " character was found in cell: " & MyCell.Address & " at position " & InStr(MyCell.Formula, "#"))
End If
Next
End Sub
However, I would like to extend this functionality to check for multiple characters, using some sort of array that contains all the characters I want to check for e.g. "#","*","£" and so on, without having to repeat the above code for each character for which I need to check.
View 2 Replies
View Related
Apr 2, 2014
How can I create a multiple if statements that returns a specific response if the cell is blank?
For instance, =if(K6=[date],"Carry Out"), if(K6=[blank],"Break Out"))).
View 3 Replies
View Related
Jul 8, 2014
The code I currently have allows me to search for one text entry in the column and then copy and paste all those entries into the next worksheet. There are 5 different text entries I'd like it to look for but can't figure out how to do it. I have a work around by using wildcards, but then I have to write another macro to delete out the ones I don't want.
Where is says "DNA - weapons", I'd also like it to allow for "DNA - paternity" and a couple other options.
Here is my current code:
Sub Copy_To_Another_Sheet_1()
Dim FirstAddress As String
Dim myArr As Variant
Dim Rng As Range
Dim Rcount As Long
Dim I As Long
Dim LastCol As String
Application.ScreenUpdating = False
[Code] ....
View 2 Replies
View Related
Jul 20, 2009
I have a problem with excel that I have been trying to solve for the past 5 days! I have 2 tables (one with 500 rows, the other with 15000) that contain information about pipes.
The smaller table, Drift List, is to be used as a reference to fill up the bigger table.
Here is how Drift List looks like:
Size NominalWeight WallThickness APIDriftDiameter AlternateDriftDiam.
5.5 14.00 0.244 4.877 7.625
5.8 14.88 0.253 4.915 ----
...
The other table, Pipe Database, Looks like:
Size NominalWeight WallThickness DriftSize DriftType
5.5 14.00 0.244
...
I have to fill up Drift Size using APIDriftDiameter from the first table, BUT if AlternateDriftDiameter is available, I have to use that instead.
DRIFT TYPE column has to indicate either API or Alternate (Depending on which one I have used).
Here are the two files:
[url]
[url]
View 9 Replies
View Related
Apr 25, 2014
So, I've created a pivot table and need to use one of my values in both the row and value fields. I haven't seen a way to easily do that so far. Is there something I'm missing or is this not possible with Excel (yet)?
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
Sep 25, 2013
If I'm using this function to find the specific text "EXPIRED" in the specified cells, how can I add additional cell ranges on the same sheet to this function?
D17:D34 as well as F17:F34
=IF(ISERROR(MATCH("EXPIRED",Homepage!D17:D34,FALSE)),?"OK","ALERTS")
View 2 Replies
View Related
May 1, 2013
I'm trying to code a macro to search through all the files in certain folders to find a value defined by the user. The rows containing that value will then be copied and pasted into a separate workbook. My boss currently planning on storing about 550 different files (90 days worth of data) between these folders, but there is the possibility of years worth of data collection, should he change his mind about the 90 days, so I'd like the code to be efficient if possible. What I've tried to do is search each file for the value, then set a range equal the first row containing that value. If the value is found in that workbook, the code loops through the worksheet, adding all the other rows with that value to the range. Then the file looping exits (the values I'm looking for are only contained in one of the workbooks) and the range is copied and pasted into the master workbook. Each workbook has only one sheet.
I'm currently getting a run-time error "13" Type Mismatch error when it gets to the line where the code is supposed to find the value and initialize the range.
Code:
Private Sub SubmitButton_Click()
Dim mybook As Workbook
Dim masterbook As Workbook
Dim rownunm As Long
Dim pathparts(1 To 5) As String
[Code] ......
View 3 Replies
View Related
Jan 17, 2014
I have multiple worksheets with part numbers and prices for different computer parts. On each worksheet I have multiple sets of part numbers and prices but I only need one the information from specific part from each worksheet. Luckily, the part number I need is also the name of whatever worksheet contains it. I want to take the part numbers and prices from the parts I need and put them in one master sheet called "PartsNumbersCombined". Currently I haven't even attempted to format the data in PartsNumbersCombined, I am just trying to actually pick up and move the correct data. Here is my code:
Sub harvest()
ShtCount = ActiveWorkbook.Sheets.Count
For i = 2 To ShtCount
[Code].....
The part numbers are in column A and the prices are one row down in column C. I feel like I am close but I can not get anything pasted on my master sheet.
View 1 Replies
View Related
Aug 6, 2009
I know how to see if one value exists in an arrray, but is there a way to see if a combination of fields in one array exist in the same combination in another array?
In other words, if I have table A:........
and table B:......
I would like to add a column to table B containing a function that would indicate, in this instance, that only Larry-San Francisco, CA is a record shared by both tables.
View 3 Replies
View Related
Dec 14, 2012
The goal of this is to find the best combination of a captain (out of 5 possible choices), 5 crewmates (out of 25, no repeats), deck items (2 items, can be repeated, 20 total options), ram (1, out of 13), hull (1, 13 choices), and rudder (1, 7 choices)
Each choice in every category has 4 attributes - fighting, morale, seafaring, and speed. The ship parts also have a true/false field called "unlocked" - the false fields obviously can't be chosen.
A mission gives numbers saying what total attributes are needed for a 100% chance of success. If the mission has 2 attributes that are required (say seafaring and fighting, for instance) then it takes whichever chance of success is lowest and counts that as the mission success chance.
Example: A mission requires 2000 fighting and 2000 seafaring - I can reach 2000 fighting but 1500 seafaring, chance of success is 75%. It would be better for me to get a combination that gave me an 87% success rate in both fields.
How do I have excel go through every combination (and is that even plausable? I would imagine that being in the billions of combination choices) and highlight (turn a cell green or something) the combination that has the highest minimum chance of success in all fields?
View 2 Replies
View Related
May 15, 2008
If i have a multi-column combobox, how can i show all my columns in the display window after clicking on an item in the dropdown list? At the moment, only the first field will display :s (also, preferably keeping my first field as the 'value' property!)
View 9 Replies
View Related
Jun 1, 2006
i have a form that i have 2 fields that need to be capatalized at all times - B6 and G6 I have used this formula to get B6 to work but for the life of me i cant get BOTH to work and the same time.
here is the formula
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("B6") 'change to your range
If Not IsEmpty(Target) Then
Target = UCase(Target.Text)
End If
End Sub
View 3 Replies
View Related
Mar 9, 2008
I have 4 colums of customer input data, Length, Width, Height and Quantity. There are over 20 rows to accomodate multiple combinations. I have several other hidden pricing pages with all possible combinations including materials, pricing etc. Assuming I am on the frst pricing page and first combination I need a formula that will match the first 3 inputs per row and if true give me the 4rth input for the matching row.
Example:
Customer input-
row 1 = 8 - 12 - 4 - 15
row 2 = 4 - 4 - 2 - 25
etc.
Order Page-
Combination 1 = 4x4x2 quantity___ (input row 2 matches 4-4-2 give quantity 25)
Combination 2 = 4x8x2 quantity___ (no input row matches 4-8-2 give quantity 0)
Combination 36 = 8x12x4 quantity___ (input row 1 matches 8-12-4 give quantity 15)
View 3 Replies
View Related
Oct 11, 2013
I am trying to write a macro to open a specific file, but need to search multiple folders within folders to find it.
The file name I need to open is "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx".
I need to drill down to the Adherence Report folder and then have the macro search through folders for each year (2012, 2013, 2014, 2015, etc) and then each month within each year (1 January, 2 February, 3 March, etc), at which point I would then find that day's file.
So far I have the following:
Code:
Sub Open_ESCL_Report()
Workbooks.Open Filename:=*****.****.****.******.comsharesPurchasingTeam XEscalationAdherence Report & "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx"
End Sub
Each file is stored in it's respective month folder as .....Adherence Report(Year)(Month)(File).xlsx
View 3 Replies
View Related
Nov 8, 2013
I would like to select all cells in column A that begin with the text "SP". Some cells will be contiguous but others will not. For instance in one case, I would like the macro to select cells with the text "SP" which would result in cells A1, A2, A3, A10, A15 being selected. I am working on building a macro that will then do other things to these cells/rows so this is the first step.
The below code will select the first cell with "SP". How would I alter this code to only search Column A and select multiple cells? or totally different code.
Code:
Sub test()
Dim r As RangeSet
r = Cells.Find(what:="SP", LookIn:=xlValues, lookat:=xlWhole)
If Not r Is Nothing Then r.Select
End Sub
View 7 Replies
View Related
Jun 24, 2008
I have been searching the boards long and hard for this solution and have yet to find that applies fully. Every day I need to search through 20 worksheets for transactions occurring on a specific date and copy the row onto a new worksheet. I would like to run a macro that would allow me to search for a specific transaction date in column C of each worksheet and if the date matches it will copy the entire row to a new worksheet.
The issue I am having is that the transaction lists are a running sum of all the transactions for each account. Therefore, the range that the macro needs to search will change daily as well. Furthermore, the date that I would be searching for could potentially appear in column A or B but I only want the row if the date matches that of column C. Also, on some sheets there may not be a transaction at all.
View 9 Replies
View Related
