Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search function
I appreciate that this is quite a large question but was wondering if anyone could help. Has anyone written or know of a search function which will display multiple results? What I would ideally like to be able to do is type in part of the information within a cell and for a function/script to look for any cells containing this information. Then display the exact contents of any matching cells. E.g. if a cell in a worksheet contained the names "John Smith" and "Dave Smith" I would like to be able to type Smith into one cell and for both "John Smith" and "Dave Smith" to be displayed elsewhere. There must be an easy way to this that I'm just missing, Vlookup and so on require that the exact information is typed in and even then will not display multiple results. Apologies for length, but it's quite a specific problem. -- se01rw ------------------------------------------------------------------------ se01rw's Profile: http://www.excelforum.com/member.php...o&userid=31403 View this thread: http://www.excelforum.com/showthread...hreadid=511698 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search function
Try this ass an example and if it works as you require adapt to your
needs: crrow = 1 'row# ofr criterion crcol = 1 'column# of criterion dsrow = 3 'start row# of data derow = 20 'end row# of data dacol = 1 'column# of data resrow = 1 'first row# of result rescol = 5 'column# of result sstr = Cells(crrow, crcol).Value k = resrow For i = resrow To resrow + derow - dsrow Cells(i, rescol).Value = "" Next i For i = dsrow To derow dastr = Cells(i, dacol).Value If InStr(1, dastr, sstr, 1) 0 Then Cells(k, rescol).Value = dastr k = k + 1 End If Next i smith Mary smith Ron Smith Mary smith blacksmith Peter Sellers smith Rod Stuart Roy black Ron Smith Gordon hunter blacksmith Hans |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Search function | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions | |||
Search function using commas | Excel Worksheet Functions |