Search Result List (grep-like Functionality)
Aug 31, 2006
how to create a simple form that would search a sheet for a word and display the list of resulting rows - something similar to Adobe Reader 7 Search functionality ?
If I was having administrator privileges on this PC, I would simply install Cygnus and use its "grep" command on a CSV file but without that possibility, it's rather cumbersome to search an Excel sheet when you need to do it repetitively, and I need to locate a specific row each time without knowing the exact content of the cell (only searching for one or another word I know the cell contains).
View 9 Replies
ADVERTISEMENT
Mar 27, 2014
I have a standard multi-page excel workbook in Excel 2013. I have built a user form that will allow for user input into my main excel page. This inpu includes many different variables, including one called "Paint". The value of paint can currently be selected from a drop down box that pulls its values from a spreadsheet in the workbook (Paint!A$1,A$50) but here's the issue. I have over 50 possible values for paint and it makes the drop box time searching for the right one kinda hard.
Here's what I want to do: Have a button beside the paint drop-down that says "Search", When the users clicks it it opens a search dialogue of some kind that will return the closest values to their search. When they click the value they want it gets entered as the selected value for the "Paint" drop-down box.
Here's my questions:
-Is this even possible ?I know enough about VBA to know that this is probably difficult in which case id still like to try.
-If it is possible. Are there perhaps any places you could point me to that would have some sort of starting point for me to work from. Remember I got the other part already I just need the search functionality.
View 3 Replies
View Related
Jan 27, 2014
By typing either first letter or first two letters in cell A2 the list box should bring up matching street names. Also listbox should manually allow to select required street name by scrolling down.
View 3 Replies
View Related
Apr 3, 2013
So basically I have an Excel sheet which has keywords that need to be entered in Google search. I need the URL of the first page of the search result that appears after that keyword is entered. IS there a macro for the same?
View 3 Replies
View Related
Nov 28, 2013
I wanted to create a multiple drop down lists (using data validation) in column B (50 in all, every 3rd line) whereby, multiple, comma deliminated, results would display in each of the cells - for use elsewhere in the spreadsheet.
I found some code (as follows) which worked perfectly for me
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
[Code].....
As I mentioned above, I don't really understand the code and all I know is that the line about halfway down "If Target.Column = 2 Then" is defining which column (B) this will work in.
The problem I am having is that I need to protect the worksheet and the moment I "protect" the worksheet, the functionality of displaying multiple values goes away and the drop-down list reverts to only displaying one of the available values.
View 9 Replies
View Related
Jul 15, 2012
What i have so far is:
-3 Sheets (Sheet1 - Search, Sheet2 - Data, Sheet3 - Result)
-Sheet1 has a userform ready to link up - this opens up when workbook and worksheet is activated.
-Sheet2 has some example data to search - the search is for column 1 (see attachment)
-Sheet3 has a template ready for the data to be pasted to.
I would like the search result is column 1 to copy the data from columns 1 to 7 to sheet3.
I want the template/layout to remain after the pasting of results. (everything protected from editing, if possible).
Once finished (ie go back to sheet1, i want the data from sheet3 to be cleared.)
View 9 Replies
View Related
May 31, 2013
I am doing a control F search on a value that does not come back with a result. But the value exists in the sheet. I have multiple columns and rows (over 22,000 rows). Are there limits to how much you can search using control F?
View 2 Replies
View Related
Apr 14, 2008
i have been using a code that "Joe Was" wrote on this forum the code search all cells for a word i type and then copy the rows where the word was found into a new sheet. my problem is that the code overwrites the rows, so only one line is shown even when the result box say it finds 10 results.
Public Sub FindText()
Dim ws As Worksheet, Found As Range
Dim myText As String, FirstAddress As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter text to find")
If myText = "" Then Exit Sub
Worksheets("Søkeside").Cells.ClearContents
For Each ws In ThisWorkbook.Worksheets......
Do
foundNum = foundNum + 1
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
Set Found = .UsedRange.FindNext(Found)
Found.EntireRow.Copy _
Destination:=Worksheets("Søkeside").Range("A2").End(xlUp).Offset(1, 0)
Sheets("Søkeside").......
If Len(AddressStr) Then
MsgBox "Found: """ & myText & """ " & foundNum & " times." & vbCr & _
AddressStr, vbOKOnly, myText & " found in these cells"
Else:
MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If
End Sub
View 9 Replies
View Related
Jan 10, 2009
I need to search duplicates in datas and the result will be in the next worksheet. Result will shows only one entry without anymore duplicate.
View 9 Replies
View Related
Mar 4, 2009
I am trying to position the cursor in a spreadsheet column based on the data within the column but am struggling with the software to do it.
What I have is:
(a) A column (A:A) containing a mix of up to 500 numbers (1 to 3 digit) and a few text entries (1 to 6 char). These form the basis of a movement log where say 100 different drivers (each with unique identity) are logged in and out, so there will be duplicates.
(b) A number of other columns logging task/times etc but the one I am interested in (B:B), contains a tick (martlet font "a") to denote the driver has departed.
What I want to do when a driver departs, is have a quick way of finding (from the top down) the row containing the drivers number (A:A) where the driver logged in, but has not yet departed (so I can tick him out!).
So from the top: find the first row (in A:A) matching a specific number/string, where B:B is also blank and position the cursor at B"x").
I have imagined a "button" with data entry at the top of the sheet being the easiest physical way of doing it but am open to offers.
Can the Cursor (or cell?) be highlighted as well, because this is not always easy to see in bright light.
View 9 Replies
View Related
Nov 2, 2009
I have a workbook with many many sheets in it. The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so). If matches are found (they don't have to be exact case), then I'd like two things to happen:
1. The rows containing the matched search criteria in the first sheet are highlighted.
2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.
I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms.
View 3 Replies
View Related
Aug 7, 2009
I use this code to look for values like V1/V2/V3/V01 in my column. If the cell contains the value, nothing happens, if not, the whole row is deleted.
View 2 Replies
View Related
Apr 6, 2009
i've been using an IF function in order to display a certain value when one or more criteria are met in one cell in one specific worksheet. here's how it looks like:
=IF(OR(ISNUMBER(SEARCH("value1",Worksheet1!A1)),ISNUMBER(SEARCH("Value2",Worksheet1!A1))),"yes","no" )
with different values and names.. but it's just to give you the idea of what kind of function it is. this one works perfectly, but i also needed another one in which the logical test range is no longer only one cell, but more, thus a selection. i wrote the same formula, but instead of the "A1" i wrote for example "A1:A20" which i thought it would work.. but it doesn't. even more surprising, when i open the insert function (the helping wizard that pops up when i click the "fx" button on the left of the function string) and i put in the logical test, at the bottom of the window it says the correct result (ie: yes). but on the actual worksheet, the cell displays the wrong result (no). i know there are no errors in the formula, so i really don't know how to solve the problem.
View 10 Replies
View Related
Jun 9, 2009
I'd like to search for text and get the column character where the cell with the found text. The combination the two 'demands'/formulae into one are giving me problems. And the fact that the column character can exist out of two characters I cannot solve.
I can get the column number with HLookup right? and it gives me the column number, not the character. I can solve this partially (and ugly even more) with =Char(((Hlookup etc)+64 (letter before A))).
View 5 Replies
View Related
Jul 26, 2006
I have a userform that with a combobox called "complaintdis". This combobox displayes all the records in column A of worksheet "ComplaintData". When user selects any record from that dropdown list, it populates the other related fields on labels on the same userform. This part is working fine.
when user is looking at any displayed record on the userform (displayed via labels), I want user to be able to add comment that will get added to the column J of worksheet "ComplaintData".
I can workout a way to send this input data from textbox (called "txtcloser") to the column J, but I am struggling to figure out a way to link that comment to the specific record.
As there are number of entries in the worksheet "ComplaintData" , user should be able to select any record (via combobox) in no specific order and leave a comment.
View 9 Replies
View Related
Jun 4, 2008
way I can search a particular column, and when results are found, copy the rows where the results are to a new worksheet? And perhaps reuse the same worksheet (so clear the sheet on every execution)
I have tried auto and advanced filter but its not really working to my liking....
View 6 Replies
View Related
Feb 24, 2014
My current problem is that I need to search two tabs of data and use the results to modify the contents of one cell in the first tab.
I'll try to be as clear as possible:
tab1 has relevant cells "a" and "b", same row
tab2 has 3 relevant cells "c", "d", and "e", all on the same row
All cells are text values - some digits, but should be treated as a regular String.
The search needs to look at cells "a" and "b", find their match in "c" and "d", then replace the original value of "b" with the value in "e" after a match has been found.
View 1 Replies
View Related
Jun 16, 2014
I Want to fetch a data as below.
ITEM RESULT
M&C_ES_F_55+_Phase1 <55>
M&C_BR_F_18-34_Phase1 <18-34>
M&C_ES_F_35-54_Phase1 <35-54>
Likewise there are many line items, wherein i want the Age should be searched in ITEM column and should be displayed in the RESULT column.
* I don't want to Use text to column method, is it possible to use IF condition along with Search formula.
View 1 Replies
View Related
Apr 17, 2014
Currently I am working on a data entry form for CRM database using Excel. Now i am stuck at filtering and displaying the data from the spread sheet to the user form.
There are three text boxes to key in the filter criteria and a button which will filter the data from the spreadsheet based on the criteria in the three text boxes. Then the whole row where the filtered data resides will be displayed on text boxes on the user form.
View 5 Replies
View Related
Apr 30, 2008
Basically, I need to look for an item, lets use "apple" as an example, in a range of cells, A1:D100. Then the corresponding result would be in another column in the same row.
View 10 Replies
View Related
May 10, 2013
When I do a SEARCH operation to find the number 10 among hundreds of cells containing data, I'm often finding the way Excel highlights the cell that meets the search result is not easy enough to see.
Is there anyway I could setup Excel so that the resulting cell that meets the search result is highlighted in a RED border or RED Fill? I would like the cell that meets the search result to scream at me with a color highlight that is not easy to miss.
View 3 Replies
View Related
Jun 3, 2008
I have a table in excel with a group of headings. (Serial Number, Model, Description, Repsonsible and a few others) What I would like to do is be able to search every cell within the range of that table. When the user clicks search an inputbox is displayed and you can enter any search term you like. if there is a match within the cell range, i want the entire row (and the heading row always at the top) printed into a new sheet to display results. There may be a match in multiple rows, and id like evrery row displayed.
View 5 Replies
View Related
Jun 6, 2014
Average.xlsxHi
I have changed the attachment to use CSV's
I am trying to create a report that I can enter the codes I am looking for and the formula will search the sheets within the report and once the results are found then find the average of those results.
What I am trying to do is search the codes from Sheet 1 under Outlet and ESA(in Blue) in sheet A,B and C. The problem is the ESA code seen in column I of A,B,C is only used when column L is empty in A,B,C.
Once the relevant codes are found I then need to look for the KPI's seen in sheet 1 B4, B6 and B8, they can be found in A,B,C in column R. then the result comes from column S in A,B or C.
Once the result is found for each code I want to find the average of them, with the answer to populate in yellow in sheet 1
I want the formula to be able to handle more or less codes as well as adjust the formula so I can add more sheets(possible D,E,F,G etc)
View 5 Replies
View Related
Apr 11, 2014
vba script to convert the text in a cell(split considering "Space" as delimiter) into multiple Rows and Single column & the resultant row's(A1:A7000) values need to be searched in Column(B) of another sheet,if the search result is false then the value in the row need to be highlighted in red.
I am able to split the values in one column into mutiple rows,but need to have all the resultant values in a single column.
i,e,.
I am able to split it as below:
Column A
Column B
Column C
[Code]....
for second situation - search I could search based on the values by using Vlookup() function,but unable to highlight when search result is false.
ex:
if the search string "ACB" in Sheet1 is not available in the sheet2, then value "ACB" should be changed into red.
View 1 Replies
View Related
Nov 7, 2013
I use the following formula in excel 2007 (and it's working perfectly in 2007) but this formula does not work if I work with the same file in excel 2003 ...w why and what I have to adjust?
=IF(SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B))=0;"not found";INDEX('sheet 2'!D:D;SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B));1))
(I'm working with the dutch excel version so it might be that ";" must be ",")
View 6 Replies
View Related
Feb 1, 2014
Regarding the 0 value excluding. I use the below mentioned code to sum and paste that data which are meet the date criteria:
[Code] ..........
So my question is that how should I change the code the 0 value will be excluded from the result list.
View 8 Replies
View Related
Aug 5, 2008
I have this formula in C7:
=VLOOKUP(A7,A$20:C$76,3)
The Table Array contains a list of 12 months, and there will be a match to the Lookup Value in A7 each month. I would like C7 to reflect the last completed match in the list (it will be updated each month). So, when June is the last completed month, C7 will return the matching amount from the Table Array for June; then when July is completed, I would like C7 to reflect the matching amount for July.
View 9 Replies
View Related
Nov 25, 2009
Its my first post in this forum. Hope you would provided me proper guide line to solve my problem
Suppose I have a column of numbers. such as
01010001
01101010
01201211
01212122
02101201
00102101
00010101
01010001
.
.
.
I want to find the possible set (groups from these number) which adds up to a constant let 03333333
let for example
if I add
01010101
01211201
01112031
Now adding these numbers results in 03333333
whatever the possible groups, should be listed.
View 9 Replies
View Related
Jun 11, 2013
I need to use SQL decode functionality in Excel. I tried but Excel won't accept decode. Is there a way to get the same effect in Excel?
Example: quite often, people use letters to represent numbers, like 1234D = 123.44, 5432G=543.27 in text files. To converter from letters to numbers, I can use decode (substr(A1,5,1), 'A', 1, 'B',2,'C',3,......,'{', 0, null).
Not sure how Excel handles this?
View 4 Replies
View Related
Mar 5, 2010
I am trying to make a drop down list based on the result of a vlookup.
What i want to do is look into a table that has country name, depot station, but i have more than one depot per country so when i look up with vlookup i only get one result back, the table looks like this.
Country Country nameDepot code Depot name 1 GBUnited Kingdom STN Stansted 2 GBUnited Kingdom EDI Edinburgh 3 GB United Kingdom EMA East midlands 4 FR France
GNO Garanoa 5 FR France MRS Marseille
How can i look up GB or United Kingdom and get all the depots listed from that country.
View 9 Replies
View Related
