Hi,
I don't know how to do that with a formula but here's a UDF that does it.
Alt +F11 to open
VB editor. Right click "ThisWorkbook' and insert module and
paste the code in
call with
=ConCat(A2:A11,D2,E2)
Where A2:A11 are the dates and D2 & E2 are the 2 lookup values
Function ConCat(rng As Range, rep As String, area As String) As String
Dim R As Long, R1 As Long, x As Long
Application.Volatile
rep = UCase(rep)
area = UCase(area)
For Each c In rng
If UCase(c.Offset(, 1)) = rep And UCase(c.Offset(, 2)) = area Then
MyString = MyString & c.Value & ","
End If
Next
v = Split(MyString, ",")
For R = 0 To UBound(v)
For R1 = R To UBound(v)
If v(R1) < v(R) Then
str1 = v(R)
str2 = v(R1)
v(R) = str2
v(R1) = str1
End If
Next R1
Next R
For x = 1 To UBound(v)
ConCat = ConCat & v(x) & " , "
Next
ConCat = Left(ConCat, (Len(ConCat) - 2))
End Function
--
Mike
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
"Andri" wrote:
Dear Mike,
thank you for your kind and response.
the formula is workable. but it only show for two dates only MIN and MAX.
the required solution, to list down all the date, from the smallest,
increasing date and ended with the Largest date.
so some records might contain several dates (3, 4, 5 or even 6dates).
how to adjust it?
TIA
"Mike H" wrote:
Andri,
As long as those are properly formatted dates try this ARRAY formula. the
lookup values are in D2 (B) and E2 (E)
=TEXT(MIN(IF(B2:B11=D2,IF(C2:C11=E2,A2:A11))),"dd-mmm-yy")&" ,
"&TEXT(MAX(IF(B2:B11=D2,IF(C2:C11=E2,A2:A11))) ,"dd-mmm-yy")
This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.
--
Mike
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
"Andri" wrote:
Dear All,
Here is the sample database.
Date Salesman Region
16-Aug-08 A N
16-Aug-09 B E
16-June-07 C S
15-Aug-07 A S
15-Apr-07 B E
4-Sep-07 D N
4-May-07 E N
6-Sep-07 A N
3-Oct-07 B W
24-Sep-07 E E
i would like to concatenate for Salesman B and Region E(east) in A1, so the
result will be "15-Apr-07,16-Aug-09"... which formula can solve it?
tried SMALL + sumproduct(but...this sum all the date in that arrays).
thank you for your kind attention and help.
respectfully,
andri