# Concatenate based on criteria

1. ## 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. ## 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

3. ## 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. ## Re: Concatenate based on criteria

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

5. ## Re: Concatenate based on criteria

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

Originally Posted by jonagpa
I would like to keep this VBA free if possible.

6. ## 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),"")

7. ## 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. ## 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

9. ## 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. ## 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!

11. ## Re: Concatenate based on criteria

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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