50380

Excel Searching and Return

Question:

I want to search an excel file that has a list of names. All of the names are in random order. I want to be able to search for a string such as "Tom" and in return get ALL of the "Tom" strings with the data attached. So if there is a list of 500 names, and there are only 15 entries for Tom, I want the formula to pull all 15 entries and output them to another area of the spreadsheet. Also, is it possible to do this and then also return all of the columns that are associated with "Tom" to complete an entire row entry? Thanks in advance.

Answer1:

Here is a simple macro to display an input box, and filter and copy the data that matches the value entered onto a new sheet.

Public Sub sortAndCopy() Dim rngFilterRange As Range Dim strSearchString As String Dim wsTargetSheet As Worksheet 'change this to refer to the sheet that contains the data Set rngFilterRange = ThisWorkbook.Sheets("Data").UsedRange 'prompt for string to filter by strSearchString = Application.InputBox("Enter value to search for") With rngFilterRange 'filter data range - assumes data is in column 1, but change the field if necessary .AutoFilter Field:=1, Criteria1:=strSearchString 'creates a new sheet and copies the filtered data - 'change this to refer to the range you require the data to be copied to .Copy Destination:=ThisWorkbook.Sheets.Add.Range("A1") 'turn off filters .Parent.ShowAllData .Parent.AutoFilterMode = False End With End Sub

Answer2:

You can use ADO:

Dim cn As Object Dim rs As Object Dim strFile As String Dim strCon As String Dim strSQL As String Dim s As String Dim i As Integer, j As Integer ''This is not the best way to refer to the workbook ''you want, but it is very convenient for notes ''It is probably best to use the name of the workbook. strFile = ActiveWorkbook.FullName ''Note that if HDR=No, F1,F2 etc are used for column names, ''if HDR=Yes, the names in the first row of the range ''can be used. ''This is the Jet 4 connection string, you can get more ''here : http://www.connectionstrings.com/excel strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" ''Late binding, so no reference is needed Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") cn.Open strCon strSQL = "SELECT * " _ & "FROM [Sheet1$] " _ & "WHERE MyField ='Tom' " rs.Open strSQL, cn, 3, 3 ''You can iterate through the fields here if you want headers ''Pick a suitable empty worksheet for the results Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs ''Tidy up rs.Close Set rs=Nothing cn.Close Set cn=Nothing

Answer3:

To see this data you could just apply a filter to the data and select the name from the name column. No need to copy data this way.

To get a copy of the data, copy paste as normal (the hidden rows don't get copied)

To automate, write a Sub to repeat these steps.

Recommend

  • In Visual Basic, I keep getting String To double Error
  • How to remove leading and trailing spaces from all cells of a excel sheet at once
  • Exceeded maximum execution time during Import of CSV
  • Jquery cookie plugin not working for Firefox
  • Saving a custom export from an Access 2007 script
  • Export a cell range into a new .csv file with VBA and save it without overwriting
  • Macro in Access to prompt for file path when importing file
  • How do you get a submit button in the Windows Phone keyboard?
  • How to share a variable between multiple modules in AngularJS
  • output multidimensional array to grid style textbox
  • Excel VBA pivot table boolean
  • kill the Excel.exe from the .vbs file
  • Why does the prompt text not appear for my InputBox using VBA in Excel for Mac?
  • How retrieve each specific column's values by looping through rows using C# from excel?
  • VBA Select Random Row In Range of Select
  • How do I fix the input allowed into an input box?
  • Slow Performance When Reading Excel With Microsoft.office.Interop.Excel
  • VBA Borders Excel Dependent on Page Size
  • How to copy a Shape to another worksheet (not as a picture)?
  • Copy and Paste Entire Row
  • Unable to save a query as a view table
  • Geo Fix not working in Android SDK 2.2
  • How to update powerpivot pivot table filter via cell reference?
  • mysql table locked after php crashes
  • ASP.NET windows authentication should always ask for credentials
  • Changing Jupyter Notebook start up folder by modifying “start in” not working any more
  • Scanner nextInt() and hasNextInt() problems
  • jQuery: How to AJAXify WordPress Search?
  • Trying to get the char code of ENTER key
  • Excel's Macro-Recorder usage
  • Sending data from AppleScript to FileMaker records
  • How to handle AllServersUnavailable Exception
  • VBA Convert delimiter text file to Excel
  • How to include full .NET prerequisite for Wix Burn installer
  • embed rChart in Markdown
  • Is it possible to post an object from jquery to bottle.py?
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • Reading document lines to the user (python)
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app