View Single Post
  #4   Report Post  
Krishnakumar
 
Posts: n/a
Default


Hi Lenny,

Try,

In Sheet1 B1,

=SUBSTITUTE(aconcat(IF(Sheet2!A1:A5=A1,Sheet2!B1:B 5),", "),",
False","")

where A1 houses Date

Confirm with Ctrl+Shift+Enter

ACONCAT is a Function and below is the code;


Code:
--------------------
Function ACONCAT(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
ACONCAT = ACONCAT & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
ACONCAT = ACONCAT & y & sep
Next y
Else
ACONCAT = ACONCAT & a & sep
End If
ACONCAT = Left(ACONCAT, Len(ACONCAT) - Len(sep))
End Function
--------------------


HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=471128