# How to find a list of names in another sheets

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

edit retag close merge delete

How are the names formated ... Are they all in the same cell, or in a row/colum? An example would be helpful.

( 2020-09-09 17:57:14 +0100 )edit

im sorry if im not clear enough, added an edit

( 2020-09-09 18:32:36 +0100 )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?

( 2020-09-09 19:50:16 +0100 )edit

Sort by » oldest newest most voted

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
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
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!"

more

'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,
use 'answer' only if you found a solution yourself ...

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]

more