+ Reply to Thread
Results 1 to 11 of 11

Concatenate based on criteria

  1. 01-03-2016, 09:38 PM #1
    jonagpa
    jonagpa is offline
    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
    Reply With QuoteRegister To Reply
  2. 01-03-2016, 10:01 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    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
    Reply With QuoteRegister To Reply
  3. 01-03-2016, 10:32 PM #3
    jonagpa
    jonagpa is offline
    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
    Reply With QuoteRegister To Reply
  4. 01-03-2016, 10:36 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Concatenate based on criteria

    I forgot to mention that you can hide those helpers if needed
    Reply With QuoteRegister To Reply
  5. 01-04-2016, 05:55 AM #5
    Tony Valko
    Tony Valko is offline
    Forum Expert 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.
    Reply With QuoteRegister To Reply
  6. 01-04-2016, 06:19 AM #6
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    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)
    Reply With QuoteRegister To Reply
  7. 01-04-2016, 11:41 PM #7
    jonagpa
    jonagpa is offline
    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?
    Reply With QuoteRegister To Reply
  8. 01-05-2016, 01:04 AM #8
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,913

    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.
    Reply With QuoteRegister To Reply
  9. 01-05-2016, 01:10 AM #9
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    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?
    Reply With QuoteRegister To Reply
  10. 01-08-2016, 04:29 PM #10
    jonagpa
    jonagpa is offline
    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
    Reply With QuoteRegister To Reply
  11. 01-08-2016, 04:38 PM #11
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Concatenate based on criteria

    Thank you for the feedback, Im sure this will help others
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1