copy & paste name into search?

Steven Koplin

Has anyone had any success copying and pasting names into queries ala name/one of/(pasted list)?


Unless i have the name exactly, including whether middle initial or full middle name, the search fails.

I need to find a way to take a list of names and find out if there is a match on the name at least.

I have an affilation for some, but RE most likely won't.


Any ideas very much welcome.


Thanks.


Steven

Zane Magnuson 2

Well, to copy/paste a list, do the following:


In Excel, highlight the desired cells within the same column and click "Copy"


In RE-Query, do a "One of" criteria. In the space where you enter the list of criteria, click the first box (as though you're about to type something). Now the key step - Scroll down in the scroll bar a few rows. Now press Ctrl+V (or paste). It should fill your list into the fields.


In order to do fuzzy search based on names...that's going to be tough. You can do "First/Last Name is like [criteria]", which allows you to use wildcards like "?" or "*", but you can only do one at a time with that. For "First/Last Name is one of" criteria, it'll look for exact matches only.



 

JoAnn Strommen

No, as you said has to be exact match unless you're using wildcards "*". 


When you say you have an affiliation for some does that mean you're wanting to search for individuals that have records or are contacts?


Is this list long? 

Nicole S

If the list of names is exported from RE using the "name" field, then the "one of" operator does work for a search like this. As to how to accomplish it when you have a list that doesn't come from RE, I'm not sure. Does your list have any other possible fields that you can use for matching, like email or phone? I have used those when I know my names are all over the place.

Tracy Morgan

Hello Mr. Magnuson,

You are now one of my favorite RE persons of
all times.  Where did you learn this?  You cannot imagine
the trouble you have saved.

Thx (to the 100^th power)

 

Tracy Morgan

Foundation Assistant

 

"Be kind whenever possible. It
is always possible." His Holiness Dalai Lama

 

 

 

---

This message is intended for the sole use of the addressee, and may
contain information that is privileged, confidential and exempt
from disclosure under applicable law. If you are not the addressee
you are hereby notified that you may not use, copy, disclose, or
distribute to anyone the message or any information contained in
the message. If you have received this message in error, please
immediately advise the sender by reply email and delete this
message.

Zane Magnuson 2

Tracy Morgan:

Hello Mr. Magnuson,

You are now one of my favorite RE persons of all times.  Where did you learn this?  You cannot imagine the trouble you have saved.

Thx (to the 100^th power)

 

Tracy Morgan

Foundation Assistant

I felt the same way when I learned it from some obscure forum post a couple years ago. I saw it and tried it, and it blew my mind. How come Blackbaud doesn't have this in big, bold letters on the front page of their website?!


"Welcome to Blackbaud - Your Database Solution Company. By the way, you can copy/paste into One Of fields!!"

Steven Koplin

Thanks everyone.


The list is 300 names long and no other meaningful data to search on.

The copy and pasting is not the problem - it's that searching by "name" always looks for "first middle last" only.


Back to the drawing board.

shani traum

How about sort name?


I believe that is "last, first"...


 

JoAnn Strommen

Not sure I'm following what you are trying to do, but incase you haven't spotted it, there is a field you can query on that's just last name.  Would pull more records but don't see how you could do one with the full name. 


Are you planning to put these records in RE if not duplicated or just looking for them.  Maybe you could do a constituent import and search for duplicates to find the ones already in RE.  Or at least run it using validation to get the exceptions.  Just thinking, like I said not sure what you're end goal is.

Tracy Morgan

Yes, simply re-format your list:

<Last name>, <First name>

I have playing with this ever since the post having quite a bit of fun!  Not sure how I missed this feature! THANKS!

shani traum

Just re-read my post, and realized I was not as clear as I could have been...


There is a query field called "sort name", which I believe is formated Last, First.


If you need some excel formulas to reformat your info in that construction, let me know - I have a several fun ones which have saved my life a time or two!


Shani

Barbara Schlichter

For lists larger than 500 records, I use a wonderful free product called SmartPASTE.  You can get it here https://www.smartthing.org/portfolio/smartpaste/   I use it daily.

Barbara


 

Zane Magnuson 2

Steven Koplin:
Thanks everyone.


The list is 300 names long and no other meaningful data to search on.

The copy and pasting is not the problem - it's that searching by "name" always looks for "first middle last" only.


Back to the drawing board.

Well, if your list is in Excel, you can parse out the first, middle, and last names by using combinations of the FIND, LEFT, RIGHT, MID, TRIM, and LEN formulas.


If your cell has something like "JOHN ADAM SMITH", you could do this:

First Name

FIND can be used to search for the first instance of a space and return the number of characters it takes to get there.

So "=find(" ",[cell reference],1)" says "Find the first space in the cell reference starting at the first character."

So if you do "=left([cell reference],find(" ",[cell reference],1))", you're basically saying "Starting from the left, give me the characters until you reach the first space". In this case, you'll get "John" in a new cell. Do a copy-drag for this down the whole column, and you'll get the first part of the name for each of those cells.

Last Name

"RIGHT" works the same way as "LEFT", but it starts at the end of the cell and moves in from there. The problem is "FIND" still goes from left-to-right, so you have to take a somewhat different approach. This time, you'll want to work in the LEN formula, which tells you how many total characters are in a cell. Next you want to find where the final space shows up, so you can say "Take all the characters in the cell, subtract out the cells up until that last space, then give me those right-most characters." This formula looks like this (I'll try to walk through each step of it below):

"=right([cell], len([cell])-find(" ",[cell],find(" ",[cell],1)+1))

• Give me the right-most characters of the cell, starting from the final space. To find the final space, tell me how many total characters are in the cell (LEN), then find a space (" ") character. I don't want you to tell me where the first space is, so find that space, add 1, then find the next space and tell me where it is. Take that position and subtract it from the total number characters in the cell, in order to tell me how many characters should be grabbed from the right-side of the cell.

Middle Name

"MID" is going to work like a combination of LEFT and RIGHT.

MID will ask you where you want to start in a cell, and how many characters you want to grab. So you'll start with the same idea you used in LEFT - by FINDing the first space in the cell. Now you want to tell it how many characters to go. To do that, you do the same thing you did to find the second space, then subtract the position of the first space. That formula (somewhat messy) will look like this:

"=mid([cell],find(" ",[cell],1),find(" ",[cell],find(" ",[cell],1)+1)-find(" ",[cell],1)))"

Cleanup

Take all your formulas and put them inside of "TRIM()". That'll remove any extra unwanted spaces you might grab along the way.


A sample Excel spreadsheet is attached. Hope this helps!