Another Stingconcat question

[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

These are the two VBA codes installed
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





You would need to add a new function to do this. Are you ok with using the
VBEditor?

Press Alt-F11
Click Insert > Module
Paste in this code (sorry, it's a little long, be sure you get it all):

===========
Function StringConcat(Sep As String, ParamArray Args()) As String
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' StringConcat
' This function concatenates all the elements in the Args array,
' delimited by the Sep character, into a single string. This function
' can be used in an array formula.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim S As String
Dim N As Long
Dim M As Long
Dim R As Range
Dim NumDims As Long
Dim LB As Long
Dim IsArrayAlloc As Boolean

'''''''''''''''''''''''''''''''''''''''''''
' If no parameters were passed in, return
' vbNullString.
'''''''''''''''''''''''''''''''''''''''''''
If UBound(Args) - LBound(Args) + 1 = 0 Then
StringConcat = vbNullString
Exit Function
End If


For N = LBound(Args) To UBound(Args)
''''''''''''''''''''''''''''''''''''''''''''''''
' Loop through the Args
''''''''''''''''''''''''''''''''''''''''''''''''
If IsObject(Args(N)) = True Then
'''''''''''''''''''''''''''''''''''''
' OBJECT
' If we have an object, ensure it
' it a Range. The Range object
' is the only type of object we'll
' work with. Anything else causes
' a #VALUE error.
''''''''''''''''''''''''''''''''''''
If TypeOf Args(N) Is Excel.Range Then
'''''''''''''''''''''''''''''''''''''''''
' If it is a Range, loop through the
' cells and create append the elements
' to the string S.
'''''''''''''''''''''''''''''''''''''''''
For Each R In Args(N).Cells
S = S & R.Text & Sep
Next R
Else
'''''''''''''''''''''''''''''''''
' Unsupported object type. Return
' a #VALUE error.
'''''''''''''''''''''''''''''''''
StringConcat = CVErr(xlErrValue)
Exit Function
End If

ElseIf IsArray(Args(N)) = True Then

On Error Resume Next
'''''''''''''''''''''''''''''''''''''
' ARRAY
' If Args(N) is an array, ensure it
' is an allocated array.
'''''''''''''''''''''''''''''''''''''
IsArrayAlloc = (Not IsError(LBound(Args(N))) And _
(LBound(Args(N)) <= UBound(Args(N))))
On Error GoTo 0
If IsArrayAlloc = True Then
''''''''''''''''''''''''''''''''''''
' The array is allocated. Determine
' the number of dimensions of the
' array.
'''''''''''''''''''''''''''''''''''''
NumDims = 1
On Error Resume Next
Err.Clear
NumDims = 1
Do Until Err.Number <> 0
LB = LBound(Args(N), NumDims)
If Err.Number = 0 Then
NumDims = NumDims + 1
Else
NumDims = NumDims - 1
End If
Loop
''''''''''''''''''''''''''''''''''
' The array must have either
' one or two dimensions. Greater
' that two caues a #VALUE error.
''''''''''''''''''''''''''''''''''
If NumDims > 2 Then
StringConcat = CVErr(xlErrValue)
Exit Function
End If
If NumDims = 1 Then
For M = LBound(Args(N)) To UBound(Args(N))
If Args(N)(M) <> vbNullString Then
S = S & Args(N)(M) & Sep
End If
Next M

Else
For M = LBound(Args(N), 1) To UBound(Args(N), 1)
If Args(N)(M, 1) <> vbNullString Then
S = S & Args(N)(M, 1) & Sep
End If
Next M
For M = LBound(Args(N), 2) To UBound(Args(N), 2)
If Args(N)(M, 2) <> vbNullString Then
S = S & Args(N)(M, 2) & Sep
End If
Next M

End If
Else
S = S & Args(N) & Sep
End If
Else
S = S & Args(N) & Sep
End If
Next N

'''''''''''''''''''''''''''''''''''
' Remove the trailing Sep character
'''''''''''''''''''''''''''''''''''
If Len(Sep) > 0 Then
S = Left(S, Len(S) - Len(Sep))
End If

StringConcat = S

End Function
===========

The code is also available here:
http://www.cpearson.com/excel/stringconcatenation.aspx

Press Alt-Q to close the editor and save your sheet. You've just added a
function called StringConcat() to your sheet and it works very simply.

If your 1000 cells are range A1:A1000, use this formula in another cell:

=StringConcat(", ",A1:A1000)

Voila! Works like a charm. Will that work for you?

 

[Jacob Skaria]

The below formula entered nomally is supposed to lookup all values in Column
C which match 'Dallas' and concatenate the corresponding email address in Col
F. If you mean you are able to return only one mail address check out whether
the entries are 'Dallas' without any spaces or try out this in a fresh sheet
with some dummy entries

=VLOOKUP_CONCAT(C13:F500,"Dallas",4,"; ")

If this post helps click Yes

 

[dlotz]

hmmmmm now I am getting a #name

Simply
Col A Col B
Dallas 1234
Dallas 1234
Austin 1235
Austin 1235

formula
=VLOOKUP_CONCAT(A1:B4,"Dallas",2,"; ")

all cell are formated as 'General" no spaces ect.

 

[Jacob Skaria]

That should bring the result 1234; 4567.. If that is #name error make sure
you have copied the function to a module within the same workbook.

If this post helps click Yes