+ Reply to Thread
Results 1 to 9 of 9

Concatenate based upon criteria

  1. #1
    Registered User
    Join Date
    04-01-2015
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    5

    Concatenate based upon criteria

    I did search the forum but haven't found a thread that appears to address this--if I missed it, my apologies.

    I'm using Excel 2010 and have 2 worksheets and want to lookup values from Worksheet1 and concatenate data in Worksheet2 Column D into a cell in Worksheet 1. I've been wracking my brain trying to figure this out and thought folks here must have encountered a similar situation.

    Constraints:
    I am unable to use Morefunc.
    I am not proficient in VBA.

    Does anyone have any thoughts?

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,041

    Re: Concatenate based upon criteria

    Can you give us some examples?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Concatenate based upon criteria

    Please attach a sample workbook.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Registered User
    Join Date
    04-01-2015
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    5

    Re: Concatenate based upon criteria

    Many thanks for your quick replies!

    Here is the example spreadsheet. The desired state is to populate column C, Extra Territories, with concatenated values from the values on the Territory worksheet's Extra Territory column B.

    Please disregard the column/row references in the OP.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,041

    Re: Concatenate based upon criteria

    Try this (backup your data first)

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-01-2015
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    5

    Re: Concatenate based upon criteria

    I should point out that the actual spreadsheets will likely grow into a few hundred rows in the Territory worksheet, and a very manageable, much smaller number on the Business worksheet. As it is, one can obviously filter as necessary with as few rows as there are on the Example.xlsx.

  7. #7
    Registered User
    Join Date
    04-01-2015
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    5

    Re: Concatenate based upon criteria

    Special K, that's beautiful! Much obliged for the code. I will need to fiddle with it a little bit--I love that it sorts the string as well!

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,753

    Re: Concatenate based upon criteria

    Quote Originally Posted by ed_han View Post

    .....Constraints:
    I am unable to use Morefunc.
    I am not proficient in VBA.

    Does anyone have any thoughts?
    You don't have to be proficient in VBA to use this User Defined Function. Copy and paste it into a module in the VBA editor. It is called Concatall. It was written by tigeravatar and shows up frequently on the Forum.

    Please Login or Register  to view this content.
    This is one way to use it. This formula must be array-entered. Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then fill down.

    Results look like this:

    Row\Col
    A
    B
    C
    2
    Business 1
    111
    619, 318, 320, 615, 306, 321, 322, 330
    3
    Business 2
    116
    355, 345, 148, 139, 140
    4
    Business 3
    355
    116, 119, 118, 220, 230, 614, 615


    Is this what you are wanting?

    Edit formula change.
    Last edited by FlameRetired; 04-02-2015 at 11:37 AM.

  9. #9
    Registered User
    Join Date
    04-01-2015
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    5

    Re: Concatenate based upon criteria

    OK, I thought I had this figured out. Wrong.

    I can't help thinking I'm missing something really basic here but I'm just not seeing it. A little help, please?
    Attached Files Attached Files

+ 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. Concatenate cells based on criteria/threshold
    By amartin575 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-19-2015, 12:26 PM
  2. Concatenate a range based on Criteria
    By nuruedriss in forum Excel General
    Replies: 1
    Last Post: 11-06-2013, 10:32 AM
  3. Pls help, can we concatenate text based on some criteria lik SUMIFS
    By Shermin in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-22-2013, 04:31 AM
  4. Concatenate Pivot Table fields based on criteria
    By dluhut in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-09-2012, 12:52 PM
  5. Concatenate multiple cells based on specific criteria
    By satkadeb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-25-2011, 08:50 AM

Tags for this Thread

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