+ Reply to Thread
Results 1 to 11 of 11

Concatenate names based on month in different cell

  1. #1
    Registered User
    Join Date
    04-27-2016
    Location
    Brooklyn
    MS-Off Ver
    2010
    Posts
    46

    Concatenate names based on month in different cell

    I have a list of names in one row and corresponding dates in another row. In a different sheet, I want to create a cell for each month and have the names that correspond with that month concatenate into that cell and be separated by commas. If possible, I want it to work so that when a new name/date is added to the sheet, it will automatically sort into that cell on the other sheet.

    Any help would be appreciated!

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Concatenate names based on month in different cell

    This will require a VBA function.

    Are you OK with that?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    04-27-2016
    Location
    Brooklyn
    MS-Off Ver
    2010
    Posts
    46
    Quote Originally Posted by Tony Valko View Post
    This will require a VBA function.

    Are you OK with that?
    I could give it a try-- haven't used one before.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Concatenate names based on month in different cell

    Try this...

    Copy the VBA code at the link below and paste it into a general module:

    https://www.excelforum.com/showthread.php?p=3096647

    With your file open...

    Right click any sheet tab and select: View code
    Goto the menu Insert>Module
    Paste the VBA code from the link into the window that opens on the right

    Close the window to return the Excel

    Then...

    Data Range
    A
    B
    C
    D
    E
    F
    1
    Sam
    Lisa
    Carl
    Brandy
    Tom
    Karen
    2
    4/1/2016
    1/22/2016
    3/12/2016
    4/15/2016
    1/1/2016
    2/8/2016
    3
    4
    5
    January
    Lisa, Tom
    6
    February
    Karen
    7
    March
    Carl
    8
    April
    Sam, Brandy


    This array formula** entered in B5 and copied down:

    =concatall(IF(MONTH(A$2:F$2)=ROWS(B$5:B5),A$1:F$1,""),", ")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    You'll have to save the file as a macro enabled file in the *.xlsm file format.

    Here's a sample file that demonstrates this.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-27-2016
    Location
    Brooklyn
    MS-Off Ver
    2010
    Posts
    46

    Re: Concatenate names based on month in different cell

    Thanks! I think I am on the right track but when I enter the array formula I am getting an error that says "Ambiguous Name Detected: Concat All".

    I might be complicating it because I am referencing cells in another sheet. My formula looks like this right now:

    =concatall(IF(MONTH(Outcomes!K$3:K$53)=ROWS(D$7:D7),Outcomes!I$3:I$53,""),", ")

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Concatenate names based on month in different cell

    Are you sure you put the VBA code in the right place?

    Ambiguous Name Detected: Concat All
    That space in the name looks suspicious.

  7. #7
    Registered User
    Join Date
    04-27-2016
    Location
    Brooklyn
    MS-Off Ver
    2010
    Posts
    46

    Re: Concatenate names based on month in different cell

    Sorry, that's my typo. There's no space in the error message.

  8. #8
    Registered User
    Join Date
    04-27-2016
    Location
    Brooklyn
    MS-Off Ver
    2010
    Posts
    46

    Re: Concatenate names based on month in different cell

    I'm wondering if part of my problem is that the first row I am listing refers to February and not January? I am not quite understanding how the formula knows which month it is tallying.

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Concatenate names based on month in different cell

    I don't think that's the cause.

    In the formula...

    ROWS(D$7:D7)

    Is used to generate the month numbers.

    ROWS(D$7:D7) = 1

    As you copy the formula down a column that increments like this:

    ROWS(D$7:D7) = 1 = month Jan
    ROWS(D$7:D8) = 2 = month Feb
    ROWS(D$7:D9) = 3 = month Mar
    etc
    etc

    Where did you put the VBA code? It has to be put into a general module.

  10. #10
    Registered User
    Join Date
    04-27-2016
    Location
    Brooklyn
    MS-Off Ver
    2010
    Posts
    46

    Re: Concatenate names based on month in different cell

    Ok, I finally got it to work! I had accidentally pasted the VBA code twice so I was confusing it .

    Thanks so much!

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Concatenate names based on month in different cell

    Good deal. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy Paste data based on Month Names
    By kittu55 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-27-2015, 09:06 PM
  2. [SOLVED] Identify values in a cell based from another sheet and concatenate the results in one cell
    By danallamas in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-15-2015, 12:00 AM
  3. Procedure to concatenate names in an office into one cell
    By Sabosj in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2014, 07:45 AM
  4. [SOLVED] Auto instert Month names for This month, Last month and Next month
    By hemal89 in forum Word Programming / VBA / Macros
    Replies: 4
    Last Post: 12-10-2013, 12:01 PM
  5. [SOLVED] Help with a system to auto populate month names based on current month
    By rosboy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-01-2012, 05:17 PM
  6. [SOLVED] Pivot: Replace month numbers with month names and sort by fiscal year
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-29-2011, 02:13 PM
  7. Concatenate based on cell value
    By Russell719 in forum Excel General
    Replies: 5
    Last Post: 04-29-2009, 01:35 PM

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