+ Reply to Thread
Results 1 to 6 of 6

Concatenate range based on Criteria

  1. #1
    Forum Contributor
    Join Date
    06-27-2013
    Location
    Nebraska
    MS-Off Ver
    Office 365
    Posts
    115

    Concatenate range based on Criteria

    Hello,

    I have a list of tools that I would like to combine into one string to report out to another worksheet. In the test book attached the list of tools is in B4:B8. In C10 I am wanting to combine a string of all of the tools that have a "Y" by them in column A. So far I haven't been able to come up with a good way to do this for a larger set of tools. The test book only has a list of 5 tools, but the actual workbook has over 100.

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Concatenate range based on Criteria

    See attached file.
    run the following macro and it will write required text string in C11.

    Please Login or Register  to view this content.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Concatenate range based on Criteria

    If you don't want to use a macro here's a formula based approach, although it's a little messy.

    in C4 and copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The last row contains your comma separated list of tools to be used.

  4. #4
    Forum Contributor
    Join Date
    06-27-2013
    Location
    Nebraska
    MS-Off Ver
    Office 365
    Posts
    115

    Re: Concatenate range based on Criteria

    Modytrane and GeoffW283 thank you for the responses. Both seemed to work for what I was doing. I think I am going to go the Macro route so I can help avoid users from messing up the formulas. What would I need to add if the start of the list can change rows? Currently the start of the list is in A4 which is why i = 4. If I was to set the top of the list as a named range, i.e. list_start, how would I code that to start looking there?

    Thank you!

  5. #5
    Forum Contributor
    Join Date
    06-27-2013
    Location
    Nebraska
    MS-Off Ver
    Office 365
    Posts
    115

    Re: Concatenate range based on Criteria

    I was able to figure something out.
    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Concatenate range based on Criteria

    What you have works fine but here's a minor simplification:
    Instead of:
    Please Login or Register  to view this content.
    You can simply write:
    Please Login or Register  to view this content.
    And thanks for the reputation points!

+ 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. [SOLVED] Concatenate based on one criteria
    By tkbuc in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-28-2017, 08:24 AM
  2. Concatenate unique value from a range with one criteria
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-13-2017, 01:31 PM
  3. Concatenate based on criteria
    By jonagpa in forum Excel General
    Replies: 10
    Last Post: 01-08-2016, 04:38 PM
  4. Replies: 10
    Last Post: 11-04-2015, 05:19 AM
  5. Concatenate Based on the Criteria
    By nawas in forum Excel General
    Replies: 1
    Last Post: 08-06-2015, 12:39 PM
  6. [SOLVED] Concatenate based upon criteria
    By ed_han in forum Excel General
    Replies: 8
    Last Post: 04-08-2015, 10:02 AM
  7. Concatenate a range based on Criteria
    By nuruedriss in forum Excel General
    Replies: 1
    Last Post: 11-06-2013, 10:32 AM

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