Searching For Multiple Data
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
ADVERTISEMENT
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
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
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
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
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
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
Jul 29, 2014
I'm trying to print score cards from the scores sheet (two different worksheet pages). Some score cards (each score card is one page) will only have 3 players and some will have up to 5 players so I can't just go down the list and use a formula to transfer data line by line. I will set the score sheet up I just all of hole 1 players to be printed on a card and so on with hole 2-18 also I need First and Last names on the cards and possibly score total by the name.
Graphic1.gif
View 5 Replies
View Related
Jul 4, 2009
I am looking to enter a numeric value these are the green columns. it then needs to search the workbook for that number (Barcode Number).
then if the cell to the right has a value, it needs to pull that number into the userform' if no number is next to it, then I manually write and post it the cell next to the one just found..
View 5 Replies
View Related
Feb 19, 2010
I am looking for some help with my data, I have this excel sheet in which i have certain abbreviations and answers to some questions which we use on our daily job. Now what i want to do is create a search box on the sheet and find the matching Abbrevation on the complete excel. The trick here is to hide every thing else when i search for some thing and see only for what i want to see in order to not to get confused. I am attaching the excel to give an idea to what i am looking for.
View 2 Replies
View Related
Feb 25, 2014
I have got a UserForm to search data from worksheet and show it in userform.
In my search userform, the data is searched by first selecting " Year " and then " Batch Number " (Two Batches runs in a Year) so I filled combo boxes with "Batch 1" and "Batch 2"
for example "Year 2013 " would have "Batch 1 and Batch 2"
"Year 2014" would have "Batch 1 and Batch 2"
In my worksheet, it has Data for "Year 2013" and data for "Year 2014" has not yet been inserted.
The Problem is that when In the Userform I select " Year 2013 " and select Batch 1 or 2 it shows data in list box and in textboxes.
But when I select "Year 2014" and select "Batch 1" or "Batch 2" it gives error. I know there is no data for "Year 2014" yet in the worksheet, but I want it should not give error in vba coding, But It should display MsgBox "No record found"
I have attached worksheet and userform. Book A1 (1).xlsm‎
View 1 Replies
View Related
Aug 20, 2012
I have very large spread sheet with alot of data. My goal in this is to create a seperate simplier spread sheet with some basic information to work with day to day chasing.
My spread sheet will consist of 1 work book but to pages page 1 the main with all the info page 2 the simplified version.
The first column in the first page consists of company names. The first column in the second page is also company names. What I want to do on the second page is take certain pieces of data from page 1 on page 2 as well.
So for example page 2 column 1 will have company name and column 2 will have certificate number. On page 1 the certificate number is column 16
What I would like is for page2 to column 2 to do is search for page 2 column 1 text in page 1 and input the value of page 1 column 16.
View 1 Replies
View Related
May 13, 2008
I'm trying to write a macro in Excel that will search certain values a Siebel database. I'm already working with a macro to ADD data to the database, so I have all the connections taken care of. But now I need to pull data from it. In looking for how to do this, I see the setsearchspec method that I need to use to set up the search then the ExecuteQuery to run it to get the pointer. But before that, I have to create the referencing object. I've noticed in several examples here and on a couple other sites, these two lines:
Set busObject = SiebelApplication.GetBusObject("Revenue")
Set busComp = busObject.GetBusComp("Revenue")
So here's my question: in all of the examples I've seen, they used "Revenue" as the argument for these methods. Does it really matter what we use there? Or this just essentially a place holder? I've looked at some of the online documentation and was unable to glean an answer from that.
View 2 Replies
View Related
Jul 22, 2014
I'm trying to see if there is a way to speed up the process of doing multiple Google searches to see if I can find a website attached to a text. I have a list of websites, and am trying to update the list to remove dead/inactive sites, and also find the URLs for the live sites.
The list looks like this (sample):
Comstock, Inc.
ConNuestroPeru
Contacto Latino
contentSutra
Core States
Crains Chicago
Crains Chicago Business
Crains Detroit
Crains Detroit Business
Credential
Crescent-News.com
Crowell Weedon
Thus there's no website attached, and I need to manually find the URL myself. There are over a thousand cells in the list.
How to speed up the process of finding a url for these websites?
View 1 Replies
View Related
Feb 19, 2009
THE UNIQUE FIELD WILL BE THE "ARN" COLUMN
Each week / month I will get updated data which I will paste into the "updated data" tab. I then have a formulated tab that pulls through the information, performes some checks then calculates payments. Some records will be ready to be paid and some wont. Anything with a total claim amount will need to be paid so
1st button - Once I have pasted my data in and the sheet has calculated I need a button that copies any row from column a to y on the formulated tab that has a total claim amount. i then need the copied rows to be pasted into the paid tab and then for them to be removed from the updated reports tab. This will just leave data with totals in the paid sheet and data with none still in the Formulated tab
The following month / week I will receive another download which I will paste into "Updated reports". This data will contain records that have already been paid and once the formulated tab has calculated these records will show in the duplicate column so....
2nd Button - Anything that has a total claim amount and has a duplicate flag needs to be deleted from the updated reports sheet so it is not paid twice.
View 6 Replies
View Related
Jan 13, 2012
I have two sets of data, and one colum in each set is titled ID numbers. I would like to search both those colums and combine the matching ID numbers in one row?
View 2 Replies
View Related
