+ Reply to Thread
Results 1 to 11 of 11

Concatenate based on criteria

  1. #1
    Registered User
    Join Date
    12-16-2015
    Location
    East Coast
    MS-Off Ver
    2016
    Posts
    33

    Concatenate based on criteria

    I have this problem, hopefully someone could point me in the right direction. I would like to keep this VBA free if possible.

    I have attached a workbook to help explain all of this.

    So I have two tables. Locations table and Fruit table. Locations table has a list of fruits and their locations, some fruits are repeated because there might be in more than one location. The fruits table has a list of fruits, no duplicates, and their locations, very similar to locations table but this one works more as a summary. do not worry if it doesn't make sense to make the tables like this, it is just an example and I am looking to use this on my situation in which it makes sense.

    So what I am looking to do is use a formula to concatenate all the words in column B when it they match column D. Another way to explain this us if the word is banana, it will look at column A to find a match, then return column B. it will concatenate all the results that match banana.

    Hopefully it was clear! thanks in advance.concatenate.xlsx

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,801

    Re: Concatenate based on criteria

    I would do this this way, with a bunch of helper columns...
    D
    E
    F
    G
    H
    I
    1
    Orders table
    2
    Orders Weeks shipped
    3
    Banana
    Loc A, Loc D
    Loc A Loc D Loc A Loc D
    4
    Apple
    Loc A, Loc B, Loc C
    Loc A Loc B Loc C Loc A Loc B Loc C
    5
    Carrot
    Loc A
    Loc A Loc A
    6
    Pineapple
    Loc A, Loc B
    Loc A Loc B Loc A Loc B
    7
    Starfruit
    Loc A
    Loc A Loc A
    8
    Lettuce
    Loc A, Loc D
    Loc A Loc C Loc A Loc C

    F5=SUBSTITUTE(TRIM(G3&" "&H3&" "&I3&" "&J3&" "&K3&" "&L3&" "&M3&" "&N3&" "&O3)," ",",")
    copied down
    G3=IFERROR(INDEX($B:$B,SMALL(IF($A$3:$A$13=$D3,ROW($A$3:$A$13)),COLUMNS($A$1:A1))),"")
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Copied down and across as needed
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    12-16-2015
    Location
    East Coast
    MS-Off Ver
    2016
    Posts
    33

    Re: Concatenate based on criteria

    THanks!
    any way of doing this with out the helper columns?

    maybe using second but Im not sure how that would work

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,801

    Re: Concatenate based on criteria

    I forgot to mention that you can hide those helpers if needed

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

    Re: Concatenate based on criteria

    Quote Originally Posted by jonagpa View Post
    any way of doing this with out the helper columns?
    Not without VBA.

    Quote Originally Posted by jonagpa View Post
    I would like to keep this VBA free if possible.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,836

    Re: Concatenate based on criteria

    Another solution is here.

    Short the A column then put below formula
    C3=IF(A2<>A3,B3,C2&","&B3)
    E3=IFERROR(LOOKUP(2,1/(A$3:A$13=D3),C$3:C$13),"")

    Please check the attachment.
    Attached Files Attached Files
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  7. #7
    Registered User
    Join Date
    12-16-2015
    Location
    East Coast
    MS-Off Ver
    2016
    Posts
    33

    Re: Concatenate based on criteria

    hmm, this gives me something to work with. So lets say that we know the maximum number of results, 5 max. could we change the index array and concatenate it all?

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,493

    Re: Concatenate based on criteria

    Try below formula in E3 and copy towards down
    Please Login or Register  to view this content.
    assuming that type order will repeat up to a maximum of 5 times only
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,801

    Re: Concatenate based on criteria

    Siva, I was thinking of that kind of thing too, buit figured a helper would ()in this case) be more efficient. Also, why E3?

  10. #10
    Registered User
    Join Date
    12-16-2015
    Location
    East Coast
    MS-Off Ver
    2016
    Posts
    33

    Re: Concatenate based on criteria

    Hello peeps!

    I ended up using one helper column concatenated with out the need of array. Here is the solution, I just wanted to share it for anyone with the same issue![ATTACH]OrdersWB[/ATTACH]

    First I added antique ID to each order by counting how many times it has appeared in the Log, thus giving unique ID on log! column C.

    Then in Orders!D, I searched for B2 (order number) & "-1" (1001-1), got the week week shipped from Log!C (W1)
    then did the same for but "-2", getting (w1). Concatenated the results.

    Then in Report! I used Orders!D and search for what weeks I wanted to see using ISNUMBER(SEARCH(.

    It worked out great and now I dont have any issues with performance due to using arrays

    thanks for all the tips guys!
    Attached Files Attached Files

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,801

    Re: Concatenate based on criteria

    Thank you for the feedback, Im sure this will help others

+ 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 Based on the Criteria
    By nawas in forum Excel General
    Replies: 1
    Last Post: 08-06-2015, 12:39 PM
  2. [SOLVED] Concatenate based upon criteria
    By ed_han in forum Excel General
    Replies: 8
    Last Post: 04-08-2015, 10:02 AM
  3. Concatenate cells based on criteria/threshold
    By amartin575 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-19-2015, 12:26 PM
  4. Concatenate a range based on Criteria
    By nuruedriss in forum Excel General
    Replies: 1
    Last Post: 11-06-2013, 10:32 AM
  5. 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
  6. 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
  7. Concatenate multiple cells based on specific criteria
    By satkadeb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-25-2011, 08:50 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