Importing information (back to RE) from an exported query

Molly Kinnison

The previous person in my position HATED RE so when we needed to create a mailing list for an appeal, newsletter, etc. she ran a query for the criteria (yes, she did use that) and exported it to an Excel document to use as the mailing list. She then edited the list from there (for the printers) but never put anything back into RE. So there was no record of who received the mailing. Is there a quick way to take these Excel lists and make some sort of note in the listed constituents profiles (an import, batch, etc.)so we accurately know who received what?! I need to go back about 3 years so need a quick way to get this information BACK into RE. Thanks!

Alex Wong

@Molly Kinnison
Unless the excel file has RE ID, you are going to have a hard time doing matching to constituent.

If you have IOM, it helps with some matching to be auto matched depending on how “accurate” you want to do the auto match, but otherwise, you will need to do manual matching by name and address.

Once you have the match part done, importing into RE is simple, if each of your mailing has an appeal code, then you can assign the constituent the appeal code for each mailing.

JoAnn Strommen

@Molly Kinnison Welcome to the BB Community forums.

Did she export their constituent ID to the Excel file? If so, my first thought would be to create a constituent query with criteria of constituent ID one of and paste in the list of ID's. There is a plugin called SMARTPaste that allows you to copy in up to 10K items. Several recent forum queries about it or search knowledgebase.

As to where to ‘note’ this you would need to look at your org's procedures. Were these asks for support? I would use the query results to add an appeal to each record. If just a newsletter, does your org want them entered as actions or are actions more personal contacts. Another option is notes - it's just a bit harder to pull data out, IMO.
You can use global add functions (if you have user rights) to easily add appeal, action…

If you do not have ID's, matching by names or other data will be more challenging. You could try using same process described for ID's with address line 1 but it may not find any addresses that have been edited since list was pulled.

I'm sure others will jump in with ideas as well.

Molly Kinnison

@Alex Wong
Thank you! Yeah, sounds like I will have to do some work with the matching (no ID info) but since a lot of the lists are similar, maybe something I can copy into different lists and then assign the appeal code.

Austen Brown

@Molly Kinnison - Create your own unique identifier to locate matches such as [Last Name]-[First 7 Characters of their Address], directly within each file. I recommend Exporting a file from RE of active constituents to use as a key - also create the unique identifier there. This is will help you speed up the matching process.

After you've found a constituent ID for each person on a list you can import in the appeal information or create a query and do it through a global add.

Excel Funcitons/Features that will help with matching:

• LEFT = Excel function that grabs characters starting the left side; after you select a cell put a comma then the number of characters to include in the output.
• CONCAT = Excel function that combines information from multiple cells into one. Cells are separated by commas within the function, if you want there to be a space or character between them include it within “” quotes within the function, separated by a comma.
• VLookUp = Excel function that grabs data from another sheet depending on a matching cell (e.g., using the Unique Identifier to bring in the constituent current ID); here's a Microsoft help doc that explains function this better than I can: https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1.
• Conditional Formatting = Excel feature that helps identify duplicate values throughout cells in a workbook; select the cells/columns you want to compare > Home tab > Styles section > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
  
  

Alex Wong

@Molly Kinnison @Austen Brown
that's a great list of excel forumla Austen provided for you to use to try to make it easier to find matches.

1 comment, if you don't already know VLOOKUP and is learning anyway, XLOOKUP is a much better version of “lookup” to learn and use. VLOOKUP has a lot of limitation and issues that were fixed by XLOOKUP.