• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search names between sheets

Danilão

New Member
Hi guys ,

How do I find names, even if abbreviated, between sheets?
My head is boiling !
 

Attachments

  • Search between sheets.xlsx
    14.9 KB · Views: 3
It looks like you're running Excel 2013. If you can use at least version 2016 you'd be able to use Power Query and a fuzzy join.
In the attached, see table at cell C1 of the Sheet1 sheet. It lists the names from that sheet in the left column, and lists the possible matches on column D from Sheet2 column A, along with a similarity score in column E. I've used conditional formatting to highlight where there's more than one possible match.

However, there is an add-in you can use for earlier versions of Excel here:
I haven't tried it!
 

Attachments

  • Chandoo55514Search between sheets.xlsx
    26.4 KB · Views: 1
It looks like you're running Excel 2013. If you can use at least version 2016 you'd be able to use Power Query and a fuzzy join.
In the attached, see table at cell C1 of the Sheet1 sheet. It lists the names from that sheet in the left column, and lists the possible matches on column D from Sheet2 column A, along with a similarity score in column E. I've used conditional formatting to highlight where there's more than one possible match.

However, there is an add-in you can use for earlier versions of Excel here:
I haven't tried it!
Thanks a lot for sugestion.
It is regrettable that in my job it isn't at least 2016, and even worse, to make downloads you need an administrator password .
 

Danilão

Is there any good reason, why do want to keep or have those Your named spaces in the end of some names?
How many parts of name should be same that it could give OK?
 

Danilão

You seems to skipped my 'spaces' questions ....

This is other kind of sample ... Press [ Do It ]-button.
Sheet1:
B-columns shows - which row has Match
C... -columns shows - row and parts match (black text) or letter match from beginning ( blue text)

Sheet2:
shows rows of Match and parts Matches
 

Attachments

  • Search between sheets.xlsb
    29 KB · Views: 7

Danilão

You seems to skipped my 'spaces' questions ....

This is other kind of sample ... Press [ Do It ]-button.
Sheet1:
B-columns shows - which row has Match
C... -columns shows - row and parts match (black text) or letter match from beginning ( blue text)

Sheet2:
shows rows of Match and parts Matches
Better than I EXPECTED !!!
Very Very Good
How did you make this code? I would like to learn
 
Last edited:

Danilão

You could start from this ... from Chandoo.org
I'm already checking...thanks again
 
Back
Top