If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
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 |
#2
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
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 |
#3
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
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 |
#4
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
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 |
#5
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
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. "Mike H" wrote: 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 |
#6
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
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 -- Jacob (MVP - Excel) "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 |
#7
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
Try this:
=IF(ISERR(MATCH(SMALL(IF((Salesman="B")*(Region="E "),COUNTIF(Date,""&Date)),ROWS($1:1)),COUNTIF(Dat e,""&Date),0)),"",INDEX(Date,MATCH(SMALL(IF((Sale sman="B")*(Region="E"),COUNTIF(Date,""&Date)),ROW S($1:1)),COUNTIF(Date,""&Date),0))) ctrl+shift+enter, not just enter copy down as far as needed "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 |
#8
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
This one slightly shorter:
=IF(SUMPRODUCT((Salesman="B")*(Region="E"))=ROWS( $1:1),INDEX(Date,MATCH(SMALL(IF((Salesman="B")*(Re gion="E"),COUNTIF(Date,""&Date)),ROWS($1:1)),COUN TIF(Date,""&Date),0)),"") ctrl+shift+enter, not just enter copy down as far as needed "Teethless mama" wrote: Try this: =IF(ISERR(MATCH(SMALL(IF((Salesman="B")*(Region="E "),COUNTIF(Date,""&Date)),ROWS($1:1)),COUNTIF(Dat e,""&Date),0)),"",INDEX(Date,MATCH(SMALL(IF((Sale sman="B")*(Region="E"),COUNTIF(Date,""&Date)),ROW S($1:1)),COUNTIF(Date,""&Date),0))) ctrl+shift+enter, not just enter copy down as far as needed "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 |
#9
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
Try PIVOT table
"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 |
#10
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
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 "Teethless mama" wrote: Try PIVOT table "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 |
|
Thread Tools | |
Display Modes | |
|
|