Numbers help involving finding matches between two sheets

[catfish743]

Hey there,

I have a work related issue where I'm trying to find common entries on two different sheets in numbers. I know of the match and lookup functions but I'm not entirely sure how to use them, especially with the dilemma I have. I possess one list of referrals that has individual names in the format (First name) (Last name) in separate columns. I have concatenated them in another column to match the layout in the other sheet I have of current clients. However, the current clients list sometimes possesses names with that are in a (Husband & Wife Last_Name) format, and so trying to match between the two would be difficult I would think. I'm trying to figure out how to either search via comparing the first list and searching within the entries of the second list and providing a third sheet with those names or at least get a check mark in another column or something that indicates that those values are shared between lists. I hesitate to simply remove one of the names from the current client list because I do not know which of the two would have been the ones to request a quote and thus would be in the referral list. I suppose I could Separate via disconcatenation (? not sure what command this is, separation?) and add even more entries to the list. I'm already dealing with 5760 clients in the current client list and 685 in the referral list. Any help would be greatly appreciated and I can provide further information if that will help. Thank you!

 

[Gregg2]

I have concatenated them in another column to match the layout in the other sheet I have of current clients.

And that is what?

 

[catfish743]

And that is what?

Well the issue is that one sheet of 800 referrals is listed ("Firstname"," ","Lastname") while the other is sometimes, but not always, ("Firstname"," ","&"," ","Anotherfirstname"," ","Lastname"). So I need to automate somehow a search for at least matches from the 800 listed in the list of almost 6000. Another Issues is that the second list includes stuff in the form of "Companyname", sometimes including spaces and special characters. Did that answer what you were asking?

 

[M-B-P]

So, you're trying to organize your catfishing game involving nearly six thousand people with over six hundred profiles???? [See username]

But in all seriousness, look into using Applescript. Using a SQL based database would probably be the best option though for managing that many entries.

I used Applescript for something similar. Even if you don't have a programming background, it is simple to use. If you are serious about going that route, let me know and I can try to help.

 

[Gregg2]

Did that answer what you were asking?

Yes it did. And my conclusion is that it would a very complicated conditional formula, and I have no idea how to create it. Not that I thought there was much of a chance, but I hope it will help someone else.