+ Reply to Thread
Results 1 to 5 of 5

Function that concatenates multiple sub-project descriptions based on project code

  1. #1
    Registered User
    Join Date
    11-01-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    3

    Function that concatenates multiple sub-project descriptions based on project code

    Seen a number of different functions to concatenate, but none that quite match what I need:

    In simple form, I would like a vba function on Summary sheet Column B which will lookup and concatenate the sub-project descriptions found on the "Detail" sheet, and if they have the project code indicated in Column A. Much like "SUMIF" formula, only "CONCATENATEIF"

    Detail:
    Code Description Budget
    AK365 Safina community project 35000
    AK365 Community develop projects 13500
    AK420 Scholarship for children 32000
    Subtotal 610500
    AS540 Education - City C complete 4 classrooms 40000
    AS540 Education - City A, 3 classroom blocks; 40000
    AS540 Education - City B add one classroom block 13000
    AS540 Education - City D need 3 classrooms 13000
    AS540 Education - City E upgrade facilities of 2 classrooms; 7000
    AS540 Education - City F 4 classrooms; 10000
    AS540 Education - City G needs 2 classrooms; 7000
    AS540 Education - City H 3 classrooms.* 10000

    To yield
    Summary:
    Code Description Budget Total
    AK365 Safina community project; Community develop projects 48,500
    AK395 Medical center 530,000
    AS540 Education - City A, 3 classroom blocks; ; Education - City B add one classroom block; Education - City C complete 4 classrooms; Education - City D need 3 classrooms; Education - City E upgrade facilities of 2 classrooms; ; Education - City F 4 classrooms; ; Education - City G needs 2 classrooms; ; Education - City H 3 classrooms.* 140,000

    Please see the attached sample workbook.

    Thanks,
    Mark

    PS. I am new to posting, so please let me know if I need to clarify more.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Function that concatenates multiple sub-project descriptions based on project code

    Hi

    How will the unique list of the Code values be created on the summary sheet? Do you want a full macro to do the creation, or will they already exist, and you only want a UDF to concatenate the relevant items?

    rylo

  3. #3
    Registered User
    Join Date
    11-01-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Function that concatenates multiple sub-project descriptions based on project code

    Thank you, Rylo,
    The summary sheet already exists and has other information besides this on it, so in answer to your question, not looking for a macro to create the sheet, but rather a formula to put on the sheet where needed that will then concatenate the appropriate descriptions. Not knowing what UDF stands for, I would assume that is what you mean.
    Again, thanks for any information you can point me to that might help. I have written other formulas, but this one was a bit of a stretch for me.
    Mark

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Function that concatenates multiple sub-project descriptions based on project code

    Hi

    Ok, insert the following code into a general module in the workbook.

    Please Login or Register  to view this content.
    Summary!B3: =myfunc(A3,Detail!$A$3:$A$26) Copy down as required.

    HTH

    rylo

  5. #5
    Registered User
    Join Date
    11-01-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Function that concatenates multiple sub-project descriptions based on project code

    Splendid! Works beautifully (altered of course to the sheet I am using).
    Thank you so much - had not thought to try the offset.Value

    Mark

+ Reply to Thread

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