Combine text from numerous cells into one, skipping blanks

[AFST]

I want to combined text from cells EJ17:EJ2516 into a single cell, but have it skip blanks.

All of the cells will contain either blank or a single word in them.

Is that possible?

 

[purekarting]

This board is fantastic. A huge thanks to all the contributors.

I'm stuck. I tried the options mentioned above, but I can't figure out how to modify the methods to accomplish my goal.

I have a range of "lookup" criteria, a separator, and a data range. I need the formula to concatenate all the values from 1 column which match the lookup cell from that row, injecting the separator between each value, but don't inject a separator for each row that matches the lookup value, but has and empty cell in the data range.

Here is my file.
https://www.purekarting.com/woo_varient_trim.xls
It has notes on the file indicating the desired result.

Can anyone help?

 

[purekarting]

Here is a link to a screenshot showing the sheet and notes:

https://www.purekarting.com/assets/woo_varient_trim.png

 

[Rick Rothstein]

Here is a link to a screenshot showing the sheet and notes:

https://www.purekarting.com/assets/woo_varient_trim.png

I get a "Webpage cannot be found" error page when I click either this link or the link in the previous message.

 

[purekarting]

Sorry about that. My new webserver won't allow direct linking to files. Let's try these links:

Image
https://drive.google.com/open?id=0B3CdLSpqBchoTVAxdlVPM0doaGM&authuser=0

Excel file
https://drive.google.com/open?id=0B3CdLSpqBchoelVWTTFwZ2w5MkU&authuser=0

 

[Rick Rothstein]

Sorry about that. My new webserver won't allow direct linking to files. Let's try these links:

Image
https://drive.google.com/open?id=0B3CdLSpqBchoTVAxdlVPM0doaGM&authuser=0

Excel file
https://drive.google.com/open?id=0B3CdLSpqBchoelVWTTFwZ2w5MkU&authuser=0

Since you already appear to have formula in place, just change the delimiter from the pipe symbol to a space, wrap that result with a TRIM function call to eliminate the multiple spaces and then wrap that results with a SUBSTITUTE function call that changes the space to a pipe symbol.

 

[purekarting]

Since you already appear to have formula in place, just change the delimiter from the pipe symbol to a space, wrap that result with a TRIM function call to eliminate the multiple spaces and then wrap that results with a SUBSTITUTE function call that changes the space to a pipe symbol.

Magic. Pure magic. Worked flawlessly. Thanks so much.

For the referrence of other people in need:

My lookup value were in G2-G5000
My data was in columns H(and I-N but each formula only checked against one column of data)
My separator was a |

This is the formula used to concatenate the first data column (in conjunction with the base VBA module ACONCAT by Harlan Grove

=SUBSTITUTE(TRIM(SUBSTITUTE(aconcat(IF($F$2:$F$5000=$G2," "&H$2:H$5000,""))," ","",1))," ","|")

followed by exectuting Shift-Control-Enter to turn it into an array formula.