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 |
#11
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
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(SM ALL(IF((MAN=$T21)*(Region=$U21),COUNTIF(TGL,""&TG L)),COLUMN(Q34)-16),COUNTIF(TGL,""&TGL),0))) "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 |
#12
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
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. -- Biff Microsoft Excel MVP "Andri" wrote in message news 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 |
#13
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
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 "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Andri" wrote in message news 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 . |
#14
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
Hi Andri
Did you try the UDF i posted? -- Jacob (MVP - Excel) "Andri" wrote: 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 "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Andri" wrote in message news 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 . |
#15
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
Hi,
Download and install the following addin - http://www.download.com/Morefunc/300...-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" wrote in message ... 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 |
#16
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
Dear Ashish,
thank you ... it solved the problem. Respectfully, andri "Ashish Mathur" wrote: Hi, Download and install the following addin - http://www.download.com/Morefunc/300...-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" wrote in message ... 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 |
#17
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
Dear Jacob,
sorry for the late response, your UDF is workable. thank you for your kind help and excellent solution. Respectfully, andri "Jacob Skaria" wrote: Hi Andri Did you try the UDF i posted? -- Jacob (MVP - Excel) "Andri" wrote: 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 "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Andri" wrote in message news 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 . |
#18
|
|||
|
|||
to CONCATENATE from the smallest date with at least 2 criteria
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 "Ashish Mathur" wrote: Hi, Download and install the following addin - http://www.download.com/Morefunc/300...-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" wrote in message ... 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 | |
|
|