Ask Your Question
1

How to find a list of names in another sheets

asked 2020-09-09 17:45:26 +0100

yalazur gravatar image

updated 2020-09-09 18:32:21 +0100

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 flag offensive close merge delete

Comments

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

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

im sorry if im not clear enough, added an edit

yalazur gravatar imageyalazur ( 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?

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

2 Answers

Sort by » oldest newest most voted
1

answered 2020-09-09 21:40:19 +0100

igorlius gravatar image

updated 2020-09-10 10:48:35 +0100

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:
sheet1 sheet2

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

edit flag offensive delete link more
0

answered 2020-09-10 08:45:40 +0100

newbie-02 gravatar image

updated 2020-09-10 08:53:21 +0100

'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]

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-09-09 17:45:26 +0100

Seen: 70 times

Last updated: Sep 10 '20