Click to go to Six Degrees From Dave Home Page

Glenn Gutmacher, “How to Convert A Vertical List of Source Prospect Data for Mass-Email”

Posted on September 16, 2007
Filed Under Glenn Gutmacher, Sourcing Techniques | 1 Comment


by Glenn Gutmacher, Senior Research at Microsoft
glenn @ recruiting-online.com

  • Read Glenn’s Linkedin Profile
  • Website
  • Q: I have a Microsoft Excel spreadsheet with thousands of names of potential candidates. I don’t intend to blind e-mail them, but I do want to process them all over time.

    The entries are all in a single column, and look like this:

    name1

    company1

    e-mail address1

    ID number1

    name2

    company2

    e-mail address2

    ID number2

    etc.

    I can’t find anyone who can tell me how to “rotate” this spreadsheet so that I’ll end up with 4 columns that look like this:

    name1 company1 e-mail address1 ID number1

    name2 company2 e-mail address2 ID number2

    ANSWER: I’m glad you don’t plan to mass-email that many, which can get you in trouble with the US federal CAN-SPAM Act. But I do agree you need to change the vertical orientation to horizontal as you indicated in order to send even small batches of messages in any email processing program.

    Fortunately, lists like this are easily fixable. The key to doing this simply is the INDIRECT function, which didn’t exist with earlier versions of Excel. (If you have Microsoft Office 2007, then you’re all set.)

    Assuming your list is in column A, with the first name in cell A1, first company in cell A2, etc., put the following in the cells indicated (values beginning with an equals sign are formulas):

    cell b1: =INDIRECT(“A”&F1)

    cell c1: =INDIRECT(“A”&F1+1)

    cell d1: =INDIRECT(“A”&F1+2)

    cell e1: =INDIRECT(“A”&F1+3)

    cell f1: 1

    cell b2: =INDIRECT(“A”&F2)

    cell c2: =INDIRECT(“A”&F2+1)

    cell d2: =INDIRECT(“A”&F2+2)

    cell e2: =INDIRECT(“A”&F2+3)

    cell f2: =F1+4

    The cells in B2 through F2 can be copied in one move all the way down (starting with corresponding columns in row 3 down thousands of rows as desired). This will instantly “rotate” (display) the data from column A as you desired.

    If you don’t want the (now) extraneous data in column A and F, don’t delete them or everything in columns B-E will be messed up! Instead, click column headings B-E to highlight the content all the way down, and copy (Ctrl+C). Launch a new blank worksheet in the same file (or a completely new spreadsheet file) and press Paste –> Paste special (not the regular Ctrl+V paste). Select the radio button preceding Values and click OK.

    You now have all the records in rows, with the formulas replaced by plain values, and won’t be messed up if you sort, move them around, etc.!

    Thanks for the question, which was different than the usual sourcing inquiries I receive! If you have an e-sourcing challenge, feel free to send it to me (blog [at} recruiting-online {dot] com for possible inclusion.

    Comments

    One Response to “Glenn Gutmacher, “How to Convert A Vertical List of Source Prospect Data for Mass-Email””

    1. Glenn Gutmacher on October 12th, 2007 10:32 am

      Thanks for sharing this with your audience, Dave! I thought this was useful & instructive, too.