to CONCATENATE from the smallest date with at least 2 criteria

[Andri]

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

 

[Mike H]

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]

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]

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.

 

[Mike H]

That doesn't work
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Jacob Skaria]

Try this 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:

=CONCAT(rngRange,intLookupColumn1, strLookupValue1,
intLookupColumn2, strLookupValue2,
intReturnColumn, strFormat As String, strDelimiter)

rngRange is the data range

intLookupColumn1 is the First lookup column
strLookupValue1 is the value to be looked up in the first lookup column

intLookupColumn2 is the second lookup column
strLookupValue2 is the value to be looked up in the second lookup column

intReturnColumn is the column to be returned
strFormat is optional if to be formatted
strDelimiter is optional delimiter. Default is space


Examples:
'1. With data in A1:C11; to vlookup 'B' and "E" from 2 and 3rd columns and
sort and concatenate each entry with " ," (space followed by a comma) the
formula would be

=concat(A1:C11,2,"B",3,"E",1,"dd-mmm-yy"," ,")


Function CONCAT(rngRange As Range, _
intLookupColumn1 As Integer, strLookupValue1 As String, _
intLookupColumn2 As Integer, strLookupValue2 As String, _
intReturnColumn As Integer, Optional strFormat As String, _
Optional strDelimiter As String = " ")
Dim lngRow As Long, arrDate() As Variant, varDate As Variant
Dim lngTemp1 As Long, lngTemp2 As Long

ReDim arrDate(0)
For lngRow = 1 To rngRange.Rows.Count
If StrComp(CStr(rngRange(lngRow, intLookupColumn1)), _
strLookupValue1, vbTextCompare) = 0 And _
StrComp(CStr(rngRange(lngRow, intLookupColumn2)), _
strLookupValue2, vbTextCompare) = 0 Then
ReDim Preserve arrDate(UBound(arrDate) + 1)
arrDate(UBound(arrDate)) = rngRange(lngRow, intReturnColumn)
End If
Next

If UBound(arrDate) > 1 Then
For lngTemp1 = 1 To UBound(arrDate)
For lngTemp2 = lngTemp1 To UBound(arrDate)
If arrDate(lngTemp1) > arrDate(lngTemp2) Then
varDate = arrDate(lngTemp1)
arrDate(lngTemp1) = arrDate(lngTemp2)
arrDate(lngTemp2) = varDate
End If
Next
Next
End If

For lngTemp1 = 1 To UBound(arrDate)
CONCAT = CONCAT & strDelimiter & Format(arrDate(lngTemp1), strFormat)
Next

CONCAT = Mid(CONCAT, Len(strDelimiter) + 1)
End Function


PS: You can modify this UDF to have the second lookup optional and to have
the sort optional...Thus this could be re-used for all type of LOOKUP()
CONCATENATE() requirements

 

[Teethless mama]

Try this:

=IF(ISERR(MATCH(SMALL(IF((Salesman="B")*(Region="E"),COUNTIF(Date,"<"&Date)),ROWS($1:1)),COUNTIF(Date,"<"&Date),0)),"",INDEX(Date,MATCH(SMALL(IF((Salesman="B")*(Region="E"),COUNTIF(Date,"<"&Date)),ROWS($1:1)),COUNTIF(Date,"<"&Date),0)))


ctrl+shift+enter, not just enter
copy down as far as needed

 

[Teethless mama]

This one slightly shorter:

=IF(SUMPRODUCT((Salesman="B")*(Region="E"))>=ROWS($1:1),INDEX(Date,MATCH(SMALL(IF((Salesman="B")*(Region="E"),COUNTIF(Date,"<"&Date)),ROWS($1:1)),COUNTIF(Date,"<"&Date),0)),"")

ctrl+shift+enter, not just enter
copy down as far as needed

 

[Teethless mama]

Try PIVOT table

 

[Andri]

Dear TM,

still figuring out your formula and Mike's formula.

related your suggestion to use pivot table, i prefer to use formula instead
of PT.

thank you and respectfully,
andri

 

[Andri]

Dear TM,

the formula is working well, but only the result the FIRST match, due
ROWS($1:1).
but intentionally, we would like to concatenate ALL date available in one
cells.

if we copy the formula to other column, it workable.

here is the tested formula:

TGL Man Region
16-Aug-08 A N B E
17-Aug-11 B E
7-Sep-09 C S
1-Feb-10 B E
15-Apr-07 B E
4-Sep-07 D N
4-May-07 E N
6-Sep-07 A N
3-Oct-10 B E
24-Sep-08 E E


15-Apr-07 1-Feb-10 3-Oct-10 17-Aug-11
=IF(ISERR(MATCH(SMALL(IF((MAN=$T21)*(Region=$U21),COUNTIF(TGL,"<"&TGL)),COLUMN(Q34)-16),COUNTIF(TGL,"<"&TGL),0)),"",INDEX(TGL,MATCH(SMALL(IF((MAN=$T21)*(Region=$U21),COUNTIF(TGL,"<"&TGL)),COLUMN(Q34)-16),COUNTIF(TGL,"<"&TGL),0)))

 

[T. Valko]

You can not do this (for all practical purposes) if you want all the results
to be concatenated into a single cell using built-in functions. You need to
use a VBA procedure.

 

[Andri]

Dear Biff,

thank you for your kind excellent advice for the practical purposes.

Message noted, we will copy the each result into the each cells.

Now focusing on the UDF module.

thank you for your kind help to all of you.

have a nice weekend

respectfully,
andri

 

[Jacob Skaria]

Hi Andri

Did you try the UDF i posted?

 

[Ashish Mathur]

Hi,

Download and install the following addin -
http://www.download.com/Morefunc/3000-2077_4-10423159.html and then use the
following array formula

=SUBSTITUTE(TRIM(MCONCAT(IF((($B$2:$B$11=B15)*($C$2:$C$11=C15)),TEXT(A2:A11,"dd/mm/yyyy"),"
")))," ",", ")

B15 has A and C15 has N

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Andri]

Dear Ashish,

thank you ... it solved the problem.

Respectfully,
andri

 

[Andri]

Dear Jacob,

sorry for the late response, your UDF is workable. thank you for your kind
help and excellent solution.

Respectfully,
andri

 

[Andri]

Dear Ashish,

thank you for your excellent External Excel Function, which is workable 100%.

but i have another issued, when the cells contains TEXT cells, the result is
little bit strange (without a comma).

here is the result:
CLOSEDCLOSED, it should be CLOSED, CLOSED
CLOSEDCANCEL, it should be CLOSED, CANCEL

please help and TIA.

respectfully,
andri