VBA Keyword Search To Return Row Numbers
i'm writing a few VB subs to help manage a database of mishaps, solutions and lessons learned, there are 11 columns and each databade entry has a separate row, there are approximately 1,000 rows.
Columns are:
ID- a unique iderntifier for each entry- integer
Date- date the entry was made- date
Project Number- a reference to an internal project file- string
Operator- company we were working for- string
Installation- where the work took place- string
Category- type of problem encountered- string
Application- field of problem encountered- string
Issue- string
Background- string
Problems- string
Lessons Learned- string...........
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Search For Keyword In Different Workbook
I am looking to write a macro that will search for keywords on a sheet, on a workbook at a specific path, and would like some help getting this macro started... It would get run from "WorkbookA", and would look at "WorkbookB" (wherever it is) and find the cell containing the keyword, where the cell value would be the output. I would really prefer that it did not open Workbook B while this macro is run, if thats possible.
View Replies!
View Related
Keyword Search Function Using SUMPRODUCT
I am trying to set up a keyword search where someone can type a word in D8 and the number of books with that word in its title will be returned. I have managed to get the following sum to work if I put the exact title in the search however I would like it to be possible for a partial search. =SUMPRODUCT(--('Books List'!G2:G1166=D8))
View Replies!
View Related
Search For Workbook In Specific Directory, Based On Keyword
I am trying to find code that locates a workbook (file) in a specific directory, based on a keyword and stores the workbook location in a string to be opened later. For example, find a workbook containing the word "ancaster" in it's file name (actual file name is "ancaster_summary_2009") in file path: "C:My Documents" and store the filename and path in a string called "ancasterBook"
View Replies!
View Related
How Can I Compare Strings To A Keyword List And Return A 3rd Value
I am trying to create my own budgeting tool. I have most of the tracker set up but I hit a stumbling block. I downloaded transations from Bank of America as a CSV file into excel. I want to categorize these transactions. I created a keyword list that has words like "Starbucks" "Exxon" etc. Each of these keywords is then listed next to a category like "food" and "gas". So I want a formula that will compare the transactions to my keyword list and return the category type. Here is an example transaction: CHECKCARD 0256 STARBUCKS USA 00029801 GAINESVILLE VA I have a keyword "Starbucks" in f2 and "food" in g2.
View Replies!
View Related
Search Partial String, Return Value In Next Column, Vba
I would like to search cells in column D for the partial string, "PIPE," (A full string may look like this: 'PIPE, 24"ODx0.375"WT API-5LX-65,ERW OR SMLS'). Then, if it's there, return the value "LF" in the corresponding cell in column C. If that string isn't found, then I'd like it to return "EA". I know this seems pretty easy, but there's a small problem. The word "BENT PIPE," could be in Column D, in which case, I would want it to return "EA" instead of "LF".
View Replies!
View Related
How To Use VBA To Search Strings And Return Values Or Cells Nearby
Im needing to search through the attached document which is truck logs at a mine and use vba to search through the list for each of the different shovels eg SHVL1, SHVL2 & SHVL3, and when the code finds that string, to display the tonnage 2 colums back from it. Im about half way there a i think but am having trouble with strings, if it was numbers i could do it no problem
View Replies!
View Related
Deleting Row If Keyword Not Found
I would like to have a macro to loop through all the worksheet except for "Summary" worksheet. The macro will look for the keyword in the worksheet starting from row 2 from every sheet. if the keyword is found on the row, the entire row will be deleted and shift row up. Keyword for example is found in H13 in the Summary worksheet Summary GH13KeywordLondon Excel tables to the web >> Excel Jeanie HTML 4
View Replies!
View Related
Format Row Based On Keyword
I have 4 macros which inserts a row in workbook (at specific interval of time). These macros run in a endless loop. The problem arises if any other workbook is open and active at the same time. The macro tries to update this active workbook. Can I ensure that macro runs for only specific workbook (whether the workbook is active or not) Option Explicit Public RunWhen As Double Public RunWhat As String Dim wCount Sub Macro3() 'Initialize counter wCount = 100 Call Macro4 End Sub Sub Macro4() If wCount > 0 Then ' If counter greater than zero then update excel with counter and ' call macro to decrease the counter Cells(1, 2).Value = wCount RunWhat = "Macro5"...................
View Replies!
View Related
AVERAGE Row Of Numbers And Return Corresponding Numeric Label
I have one Row that houses numbers 80-90 in seperate cells (11 columns A20: K20) - these are my Numeric Labels. I then have several other Rows that span the same number of columns as the Numeric Labels (A21:K100) and house Numeric Values that relate to the Numeric Labels. I would like to AVERAGE the Numeric Values in each Row and have a Numeric Label (value) that corresponds to the calculated average returned as the result. Example: Sample Data A20:K20 (Numeric Label) 80 81 82 83 84 85 86 87 88 89 90 A21:K21 (Numeric Value) 07 06 05 03 09 09 00 02 04 12 10 Based on the above data - the Expected Result is 81 Average = 6; Return Numeric Label = 81 The leading zeros shown in A21:K21 is for alignment purposes only. I would appreciate two formulas: 1) Includes zeros to be averaged 2) Excludes zeros from being averaged I have tried variations on this =INDEX($A$20:$KJ$20,MATCH(AVERAGE($A21:$K21), $A21:$K21,0)) but receive the #N/A error message.
View Replies!
View Related
Vba Vlookup And Return Row Number
I am trying to create a macro and I need it to return the row # based on a vlookup of a date and not 100% sure how to do that.. Hoping to get a little assistance. I am looking up a date in column A, and need it to return the row #, then I can do the rest of the macro I need. The date I need to look up varies, and is based on the following function I have in a cell elsewhere, but if there is an easier way in vba to write i'm open: =IF(WEEKDAY(TODAY())=2,TODAY()-3,TODAY()-1) which basically says if it's monday lookup the date from 3 days ago (friday), otherwise look up yesterday's date. So today it returns 6/8/09 and I need to find the row that has 6/8/09 as the value in column A. Now the dates in column a, are all formulas that say previous row's date + 1, so the only row that has the actual date in it is the very first one, then we just add a day. Not sure if that matters, but figured I'd give all info.
View Replies!
View Related
VBA: Search Function: Which Shows An Input Box Where You Can Enter A Word To Search For
I'm from Bavaria, Germany. Right now, I am doing an internship for my studies. my problem: I need a search procedure which shows an Input Box where you can enter a word to search for. It should work like the original Excel search (Ctrl-F), but with a simpler design, like with my own Text "Enter your Query" and a Button "Submit Query" / "Quit search". Is there the possibility to Highlight the Search Target? The problem hereby is that this highlighting should not be permanent. That means the user sees the target for which he searched for, the cell highlighted in a different color, etc. But as soon as the user clicks onto another cell, etc., the highlighting vanishes. If there is no fitting match, there should be a MessageBox like "Sorry the Target xyz cannot be found"
View Replies!
View Related
Search And Return A Value
In the attached file, I am trying to lookup what vendor is assigned to a part, and enter the vendor number in column J, K or L, depending on the number that is assigned. For example, the top part number, RAD001, has a 2 in column N, indicating vendor # 015354 is the Alternate vendor # 1. It has a 1 in column V, indicating 034180 is the Primary Vendor, and it has a 3 in column Z, to indicate that 015229 is the Alternate Vendor # 2. What formula would I need to put in cell J2 so that if a 1 is found in row 2 it will return the value 1 position left of where the 1 is found? I will then put a similar formula in columns K and L to return the vendor number if a 2 or a 3 is found. The end result will be that for each part number you will be able to see what vendor is primary, alternat 1 and alternate 2. I want to make this a formula because the actual sheet will be thousands of part numbers long, and I will need it to update the vendor information if the primary vendor is changed.
View Replies!
View Related
Search Value And Return An Associated Category Name
I'm attaching a spreadsheet in order for you to understand my question, and I will explain it based on that, ok? I apologize in advance for english mistakes, since it's not my 1st language. I want to search the salary value in column B and, in Column C, I need the job title to be posted based on what is written in Column J. The table h2:i8 contains the maximum salary a person on that position can earn. So when you look the salary on column B, it should seach in column H and compare to the maximum value, and return the description on Column I that corresponds to that salary range. So if somebody earns 6500, it should be qualified as a supervisor, because its salary maximum is 7100. I have searched the forum here and found similar problems, with suggestions to use vlooup and Index. But I just can not figure it out.
View Replies!
View Related
Search And Return Address For All Matches
I'm looking for a Formula in order to retrieve all addresses of all matches equal to the search criteria located in cell: E2 The results are hand-typed in col. G This is a case of a TWO-DIMENSIONAL "Arbitrary Lookup" Arbitrary. The range A1:C10 was named: TBL and the formula should refer to TBL as it can be dynamic in size. PS: can anybody tell me why I don't get any E-Mails when an answer is posted?
View Replies!
View Related
Search Engine Will Return All The Information
i want is to create a search engine in excel that allows users to input the clients name and the search will return all the info on the client such as the issue and their policy number. Is this even possible? Using Access is not an option. I know I can use Access but most of my users dont know how to use Access and I want to make things as simple as possible. Thanks so much in advance who ever succeeds at provding an answer u're a genius cuz I have no clue where to start lol.
View Replies!
View Related
Search Tabs And Return Results
I would like to summarize all "yes" respose contained in 15 tabs. See the attached. I would like results to be returned to the "summary tab" when user answer "yes" in the signficant column in the "process activity 1 through 15" tabs. If the answer is yes, I would like to pull the process name, aspect and impact detail.
View Replies!
View Related
Search In TextBox, Return In Listbox
I'm using a Textbox macro to search my database for a specific date, and return the company name of all entrys for that date, into a ListBox. Now this is the only way I'm prepared to look at doing, and I have managed to do it...... partially - as stupid as it sounds, I cant get multiple results to list in the ListBox itself, and for the life in me I cant find out how to do it. Also, once the options have been brought back into the ListBox, I then need code which will then populate further locked TextBox's which the rest of the company info, when selected from the ListBox. I know its asking alot (or maybe not) but I believe, (unless ive done it a really awkward way, I'm not too far away, I just dont know the code to enter, to be able to do it. Private Sub CommandButton1_Click() Dim Nullstring Application.ScreenUpdating = False If TextBox1.Value = "" Or Nullstring Then MsgBox "Please enter a date to search for" GoTo error1: End If.....................................
View Replies!
View Related
Search A Table, Return An Array
0000AAAAABBBBBCCCCCDDDDD 1111SugarRaisinDirt 2222MilkWaterWaterFlour 3333FlourSugarDirt 4444EggsWaterFlourSugar 5555WaterMilkDirt I looked for a way to represent my data in a cleaner way than this but couldn't find any tools....a point in the right direction would be helpful. I guess the old plug-in that converted Excel->HTML isn't available? Column A is a finished product and B-D are the ingredients to make it however values in Column A are also ingredients (sometimes multiple times). I want a formula that searches for values in Column A within the table B1:D5 and returns an array, if possible, of the finished products where Column A was used. For example, searching for Sugar would return "Flour, Water". Searching for Water would return "Milk, Eggs" (I'd rather not have Milk listed twice but beggars can't be choosers).
View Replies!
View Related
Return Cell Containing Search Criteria
If I have a 'key' value in a cell in one sheet, i want to use that value to find the cell in another sheet containing the 'key' and return the row number of the cell, if more than one value then I would like to be able to loop through all the rows containing that 'key' value returning the row number of each hit, kind of a programmatic version of vlookup?
View Replies!
View Related
Search Column And Return Values
ok I have multiple columns where Col-A can have any number between 1 and 5. Col-B has a 3 digit number in it. i want to search all of column A and determine if there is a 1 in it, if so i want it to print the value of col b into a specified area. here is an example of my form. COLA | COLB | -------------- 1 | 324 | -------------- 2 | 325 | -------------- 1 | 327 | --------------- this is basics, i will be using this to search col-a, determine what number is in the column, then print the number found in col b, on another page in a specific place, on the other page i have squares labeled 1-50. in a grid pattern, 10 squares per row. im trying to get all the entries in col-a, that are a 1, to put whatever value is in col-b in the first box of the grid, whatever is found in col-a with a 2, the value found n col-b of that row, into the grid box labeled 2 etc......
View Replies!
View Related
Search For Text Return Value In Next Column
I'm trying to create a spreadsheet using several others in several different formats. What i need to do is search for a text string eg "EVP" in one column and return the numerical value of the cell in the column next to it - then sum all the values and create a table in the new sheet eg. EVP sheet1!(value)+sheet2!(value)...etc...
View Replies!
View Related
Formula To Search The Numbers
= SUMIF( 'Worksheet'!$A$6:$A$22,"*Living*",'Worksheet'!$P$6:$P$22) But i has one a major defect, it doesn't search the whole worksheet, the reason why i limited the search area (Worksheet'!$A$6:$A$22) is because this formula must only find the word 'living' under one part on the worksheet which its rows have the number 3 in them. So from A6 to A22 all rows have the number 3 in column D but after the A22 the number changes to 4 and so on. Is there a way that the formula can search the number 3 in Column C from C6 to C500 first, then find the word Living from A6 to A500 then sums the rows the formula has found from P6 to P500.
View Replies!
View Related
2 Columns Of Numbers To Search
I have 2 columns of data (Column A and Column B). I would like to be able to search the two columns and see if any of the data repeats in either column. Here's an example: 15844721565538 15907771565575 15936501574897 15946991576501 15970061576502 16095501578841 16124741578842 16124781578843 16124821578844 16166161578954 1616781 1616781 The list is much longer than this. I need a formula or whatever that would scan both lists and say, nothing matches, or 1616781 matches or highlight the matches or whatever, just so long as I don't have to go down the entire list multiple times. I would have downloaded Htmlmaker and provided a glimpse that way, but the web site is blocked here at work.
View Replies!
View Related
Find To Search And Return Multiple Dates
I am working with a macro into which you input a date, the macro searches for that date (in column D of the data sheet), then copies any row with that date onto a new sheet. This works great, but now I need to be able to search for more than one date at a time and return any rows that cantain ANY of those dates. For example, I would want to search for any row containing 01/01/07 - 01/07/07 or any row containing 01/01/07, 01/02/07, 01/03/07...01/07/07.
View Replies!
View Related
Search For First Instance Of Text & Return The Value
I have a list of product numbers in col. A. In col. O I have a list of file names that contain the product numbers as well as additional characters. I need a formula that will search col O for the first instance of the text in cell A2 and return that value. The next formula will return all instances that contain the text found in A2.
View Replies!
View Related
Search A Column & Return Many Results
I have a workbook with two worksheets. On Sheet1 I have two columns 'Date' & 'Serial Number' I want to search the 'Date' column for entries that contain todays date & then display the corresponding 'Serial Numbers' in the first column of Sheet2.
View Replies!
View Related
Search And Return Values In A Range Group
I have a workbook that has rows of data starting at A5:G5 seperated in groups of 25 rows. Example: A5 is numbered 1 and A6 is 2 A7 is 3 and so on to 25 then skip a row and start numbering again to 25. What this is for is employees are placed in groups of 25 to be eligible for an award for safety. What I want to do is Range G5:G650 is to be a place where an "X" is placed beside an employee who has had an accident then Range H5:H650 would return "Employee had an accident" I already have this part working. Then I want to code a subroutine that would search for the "X's" and return the entire row data for each name in that group of 25 on a seperate worksheet. This would then be printed so we would know which group's to exclude. I know how to make it return the row of data containing the "X" but how can I make it grab the entire group? There will be multiple groups of 25 each and each time an "X" is found within a group create a new worksheet for that group. 650 employees / 25 = 26 groups
View Replies!
View Related
Search Multiple Files & Return Corresponding Value
I have a bunch of quotes in Excel format which have 5 columns ; Part number, Description, Qty, Cost, Total Cost, Unit List,Total List. I need a piece of code to open many excel files, look for a "part number" beginning with ED,DS,AP,MP or MDS and return the corresponding value in the "total cost" column then record the value in a separate spreadsheet.
View Replies!
View Related
Search A Cell For A String Of Numbers
I have the below data to look at every day. The Data codes column A contains a part number and the Info Column B, the information about the part. I have in column C the serial numbers that change daily, I copy and paste this column in everyday.What I would like to do is search within the serial numbers for the data codes and return in the Result column D the info relating to the data code. Data codesInfoSerial numberResult118Type A118F2041Type B118F BNG59617Type C2041F2151Type D2066F=9617570Type E2151F800Type FEXPEDITE COPY:570F=MEDIACOM PDF687Type GEXPEDITE:118F Currently I spend hours removing all of the text and symbols (there are normally 2500 to 3000 serial numbers) and then do a simple vlookup. Is there a formula that could search the serial number to find the data code? Or some code I could use in a macro to do the job for me? The serial numbers change daily but the data codes and info in the first 2 columns remain contstant - there are nearly 250 data codes.
View Replies!
View Related
Formula: Search Through A Column Of Numbers
I would like to be able to search through a column of numbers (say A1=2, A2=4, A3=1, A4=7, A5=5, A6=2, A7=3) and for the formula to list all the possible combinations of cells that add up to a particular number, in this case we will say 7 (i.e. A4 … A1+A2+A3 … A2+A3+A6 … A2+A7 … etc.).
View Replies!
View Related
Search For Text Within A String, Lookup And Then Return Item
I have a spreadsheet! I have two sheets, one of which contains film names and the other contains our tag structure for our website (which is a list of tags, their keywords and the primary parent channel they live under). What I am trying to do is search the film titles and if any of the words match either the tag name or keywords then return the relevant channel. Example: Sheet 1 - Films How To Apply Bridal Makeup How To Fight A Donkey How To Write Excel Formulae Sheet 2 - Tags Channel / Tag / Keywords Tech - Microsoft - windows vista xp word excel Tech - Computers - internet pc Tech - MP3 Players - iPod Zune So, for film 3 on Sheet 1 it would recognise the word Excel in the keyword list and return the channel Tech.
View Replies!
View Related
Vlookup: Search For Value In SECOND Column Of Table Array And Return Value From FIRST
I'm looking for a formula to search for a value in the SECOND (instead of first) column of a table array and return a value in the same row from FIRST (instead of the same or another) column in the table array. Formula would be searching for the unique production order number in the column B and return production line id from the column A. A1 production line_id B1 poduction_order_number A2 L1 A3 L2 A4 L1 B2 505212 B3 504234 B4 505663 I was trying vlookup(504234;B2:A4;2;0) to make formula go search from right columns to the left but then excel is switching the search table to A2:B4 and gives #N/D! One remark-there is no possibility to switch these 2 columns to simplify. I have to leave them as they are.
View Replies!
View Related
Search Table & Return Heading Of Found Value
In a large spreadsheet that receives external data, I have codes (U, N or V) allocated at various times to different rows as shown in sample worksheet attached. Elsewhere in the spreadsheet, I need to display (for later export) these codes along with their respective number in a sorted list. Please review the attached: In the spreadsheet you'll see a column of codes, the next column is the data reference number, then to the right is three columns, one for each code. As displayed in the sample book attached, each Code column is to display the data reference number (from column 2)that matches that column's code. What formula can I use to list these numbers in the appropriate columns?
View Replies!
View Related
Search Range, Return Adjacent Cells Of Matches
I can solve my problem with a pivot table, and with VBA easily...however, I'm interested in knowing if this can be done with formulas (array formulas using index/match I'm assuming). Goal: On sheet1 I have one column with products, then the column next to it will have an "Y" in it if the product is to be selected (blank if not). On sheet2 I want to create a list of the products that were selected (having the "Y"). The only thing stumping me is that I do not want spaces between the product list on sheet2...just a nice continuous list. Example: Sheet1 cup Y bowl Y spoon fork knife Y Sheet2 cup bowl knife ..not.. cup bowl knife
View Replies!
View Related
Search For A List Of Numbers And Alter Findings
Is there a function or macro that can take a list of about 200 numbers and search for these numbers throughout the workbook, If 1 of these numbers exists anywhere in the workbook, it changes the color of the number or does something to the number where I will know this number is part of the exception list? The list of 200 numbers in non changing, however the numbers I want searched will change daily. There will also be worksheets added and taken away that contains these numbers.
View Replies!
View Related
Combine Numbers In A Row From A List Of Numbers
I have to create lists of data in the following format: 123121321,12321215,121351213 (numbers with , with no spaces) These numbers are sent to me in excel in columns and I need to convert the columns into rows using the format above. The formula I have been given: =INDIRECT(ADDRESS(((ROW()-1)*10)+2,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+3,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+4,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+5,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+6,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+7,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+8,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+9,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+10,1,1,TRUE,"SSOs")) & "," & INDIRECT(ADDRESS(((ROW()-1)*10)+11,1,1,TRUE,"SSOs")) currently I only get 10 numbers per row I need at times for up to 500 numbers to be listed on a row. However even when I try to extend the formula it still only gives me 10 numbers per row and breaks up the 500 numbers to 10 per row. Is there another way to combine up to 500 numbers in one row?
View Replies!
View Related
Lottery Search Query: Correct Sequence Of Numbers
I am setting up a spreadsheet with two columns. The first column will contain a persons name the second column will contain four numbers. Name Numbers Joe 1,3,12,24 John 4,12,23,24 Jill 6,14,19,26 I need to be able to search to find the following: the correct sequence of numbers e.g. if 1,3,12,24 were pulled out then that search is easy. If I was looking for someone who had the numbers 1,12,24 then I have a problem. I was thinking about putting the four numbers in to four separate columns but my Excel knowledge is poor.
View Replies!
View Related
Search Range For Current Month & Year Return Corresponding Values
I have a workbook with two spreadsheets in it. On the second sheet there is a large table, which column headings are months and years (e.g. Jun-07 Jul-07, Aug-07, Sep-07....). I was trying to write an excel vba code that would search the first row (column headings) to find the current month and year, and copy the corresponding column along with two previous columns (months) to the first sheet. I would like to have a code that will be able to do this in Jan-08, Feb-08, or Jan-09 as well.
View Replies!
View Related
|
TRACKER
