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 SubAnswer2:
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=NothingAnswer3:
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.