Results 1 to 11 of 11

Concatenating unique cells (vertically), leaving "; " between results (for emails)

Threaded View

  1. #1
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Concatenating unique cells (vertically), leaving "; " between results (for emails)

    Hi all
    I'm creating a macro to compile some emails but taking the necessary addresses information from the spreadsheet.

    I have some email addresses in lets say column H (row 8 downwards). Lets say as per example below.

    ----------------- ------------col H
    Row 8--------------- empty cell-----------
    Row 9--------------- empty cell-----------
    Row 10------- John.smith1@hotmail.com
    Row 11------- John.smith1@hotmail.com
    Row 12------- John.smith1@hotmail.com
    Row 13--------------- empty cell-----------
    Row 14------- John.smith14@hotmail.com
    Row 15------- John.smith18@hotmail.com
    Row 16------- John.smith24@hotmail.com
    Row 17--------------- empty cell-----------
    Row 18------- John.smith30@hotmail.com

    Now, I'm after a macro or formula that would look the information from H8 (inclusive) downwards as far as per last cell used in column A. Now it should take all the unique email addresses and concatenate them together and use "; " (read semicolon + 1 space) and populate this into cell H3.

    In our example the correct result in H3 would be like this:
    John.smith1@hotmail.com; John.smith14@hotmail.com; John.smith18@hotmail.com; John.smith24@hotmail.com; John.smith30@hotmail.com;

    But not like this:
    John.smith1@hotmail.com; John.smith1@hotmail.com; John.smith1@hotmail.com; John.smith14@hotmail.com; John.smith18@hotmail.com; John.smith24@hotmail.com; John.smith30@hotmail.com;


    I have attached and example spreadsheet. It has sheet "Before", "After" and ""But not like this"

    My intention is use some email compiling macros, that would take the email addresses from H3.

    Does anyone know how to achieve this sort of concatenation?
    Any help is greatly appreciated.
    Cheers
    Attached Files Attached Files
    Last edited by rain4u; 09-21-2011 at 08:36 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1