StringConcat(", ",A1:A1000)

[dlotz]

question,
I have this VBA in my excel, could incorporate an if functio in this as well.

The basic need is:
I have a directory, bout 8 col and 500 rows.
The last row is an an email address, I use the stringcocat to considate
all the the email address i need to copy and past for distribution email list.

Each person on the directory has an office name,
Is there a way I could get the function to considate all the email address
associated with the office name \.

ie,
in the range if col c; says Dallas, stringconcat all the email addresses in
col e
Please advise

 

[Jacob Skaria]

There is no inbuilt functionality to do this. You will have to use a UDF
(User Defined
function). From workbook launch VBE using Alt+F11. From menu Insert a Module
and paste the below function.Close and get back to workbook and try the below
formula.


Syntax:
=VLOOKUP_CONCAT(rngRange,strLookupValue,intColumn,strDelimiter)

rngRange is the Range
strLookupValue is the lookup string or cell reference
inColumn is the column to be concatenated
strDelimiter Optional . Default is space

Examples:
'1. To vlookup 'jacob' and concatenate all entries of 2nd column
=VLOOKUP_CONCAT(A1:B10,"jacob",2)

'2. with lookup value in cell C1
=VLOOKUP_CONCAT(A1:B10,C1,2)

'3. with delimiter as comma
=VLOOKUP_CONCAT(A1:B10,C1,2,",")

In your case the formula would be

=VLOOKUP_CONCAT(C1:E1000,"Dallas",3,",")

Function VLOOKUP_CONCAT(rngRange As Range, _
strLookupValue As String, intColumn As Integer, _
Optional strDelimiter As String = " ")
Dim lngRow As Long
For lngRow = 1 To rngRange.Rows.Count
If CStr(rngRange(lngRow, 1)) = strLookupValue Then _
VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _
rngRange(lngRow, intColumn)
Next
VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, len(strDelimiter)+1)
End Function


If this post helps click Yes

 

[JBeaucaire]

If you already have STRINGCONCAT installed, then use it with the following
formula to get all the "dallas" matches in column C to bring in the email
addresses in column E:

=STRINGCONT(",",IF($C$1:$C$1000="dallas", $E$1:$E$100, ""))

That is an array formula, confirm it by pressing CTRL-SHIFT-ENTER, not just
enter or it won't work.

Does that help?

 

[dlotz]

Sorry it toook so long to get back,
Hope you guys get this, but I have tyhe STRING CONCAT VBA installed
and it works great witha =STRINCONCAT(a1:a100,"; ")
you know it considates the whole range with ; in one cell

I copy and pasted the vlookup_concat VBA and no matter how I plug in the
the actual formula it will only produce one email address from the array
What I find strang is the email address it produces is actually the second
in series
=VLOOKUP_CONCAT(C13:F500,"Dallas",4,"; ")
is only producing one of the email address in specified range




So if all my office "names" are in col C and the email are in Col F
Say there is Dallas, Austin and Seatlle, 100 rows down, and F has ind. email
address
FYI Col C is sorted A-Z

=StringContcat(",",IF($C$13:$C$500="Katy",$F$12:$F$500,""))

this is the other suggestion with eith String concat, same result only one
email address,
and I did crtl shft enter too btw
please advise