i have a list of names and i want to find those names in another sheet. how do i do this without manually inputing each name by ctrl+f?
the names are all in a single row and so is the other sheet
How are the names formated … Are they all in the same cell, or in a row/colum? An example would be helpful.
im sorry if im not clear enough, added an edit
I think i could throw together a maco function, which as input gets a cell range, and looks for cell ranges with identical data and then outputs their location. Would that work for you?
Hello (again)
here the (promised) macro function. Just put it into Tools → Macros → “Edit Macros” and you can call it from inside any cell via
=FRM("A1:B1")
- The 1st argument is the range to be searched for quoted as a string. The range can span multiple rows and columns.
One limitation i have to meantion. The area that is searched on each sheet is “only” A1:Z100
If that is not sufficient you can easily increase it or make it a second argument to the function.
Proof of work:
Here the macro function you have to import:
REM (F)ind(R)ange(M)atches
Function FRM(range as string)
Dim out as string
Dim start as string
Dim match as boolean
Set oConv = ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
Const maxSheetSearchRange = "A1:Z100"
Set oASheet = ThisComponent.CurrentController.ActiveSheet
Set oMRange = oASheet.getCellRangeByName(range)
start = oMRange.getCellByPosition( 0, 0 ).String
Set oSheets = ThisComponent.getSheets()
For k = 0 To oSheets.getCount() - 1
Set oSheet = oSheets(k)
Set oRange = oSheet.getCellRangeByName(maxSheetSearchRange)
For i = 0 To oRange.Rows.getCount() - 1
For j = 0 To oRange.Columns.getCount() - 1
Set oCell = oRange.getCellByPosition( j, i )
if ( oCell.String = start ) then
match = True
For l = 0 To oMRange.Rows.getCount() - 1
For m = 0 To oMRange.Columns.getCount() - 1
Set oMCell = oMRange.getCellByPosition( m, l )
if ( not (l = 0 and m = 0) ) then
Set oCell2 = oRange.getCellByPosition( j+m, i+l )
if ( oCell2.String <> oMCell.String ) then
match = False
Exit For
endif
endif
Next
if (match = False) then
Exit For
endif
Next
if (match = True) then
oConv.Address = oCell.getCellAddress()
out = out & "Match: " & oConv.PersistentRepresentation & chr(10)
endif
endif
Next
Next
Next
FRM=out
End Function
Hope that helps.
To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!
Have a nice day and let’s (continue to) “Be excellent to each other!”
‘kiss’ solution - ‘keep it simple and stupid’ (aka 'quick ‘n dirty’):
assuming: ‘all in one row’!, names to search ‘for’ in Sheet1.1:1 (row1), names to search ‘in’ in Sheet2.1:1 (row1), otherwise adapt to your ranges / names,
put =IFERROR("Sheet2."&ADDRESS(1;MATCH(A1;$Sheet2.1:$Sheet2.1;0);4);"not found") in Sheet1.A2, copy as wide as needed,
ok?
P.S. what do you want to do once you found the names? just know if / where it is? do some edit? delete? if it’s useful to have quick access to the cell may be someone can change the formula to include a hyperlink which on click will jump to the search result?
P.S. II tried it myself, you may try: =HYPERLINK("#"&IFERROR("Sheet2."&ADDRESS(1;MATCH(A1;$Sheet2.1:$Sheet2.1;0);4);"not found");IFERROR("Sheet2."&ADDRESS(1;MATCH(A1;$Sheet2.1:$Sheet2.1;0);4);"not found") & " - ctrl-click to enter") worked here, but ‘waw’! - without any warranty’
P.S. III ‘solved marks’ and ‘likes’ welcome,
click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved,
click the “^” above it if you ‘like’ the answer,
“v” if you don’t,
do not! use ‘answer’ to add info to your question, either edit the question or add a comment,
use ‘answer’ only if you found a solution yourself …
[edit]
P.S. IV just had the idea you could have meant ‘the names are each in a single row’ instead of all … in that case place the formula besides the name column and try to adapt it to search columns instead of rows …
[/edit]