Is INDEX MATCH CONCATENATE Possible?

[Alex O]

I'm using the array below to deliver the most recent comment from a list of accounts. However, many of the entries have multiple rows that correspond to my returned value. Is it possible to edit my array so all cells that match the most recent date are combined (CONCATENATE)?

=INDEX(Comments!$F$3:$F$5783,MATCH(1,MATCH(Comments!$A$3:$A$5783,'Report-Most Recent Comments'!D227,0),1))

	A	B	C	D	E	F	G
5517	593612	36886626	2/10/2017	21:32:53	SHW	"01/31/17 left vm JG.1/9/17 Collection email sent by
5518	593612	36886627	2/10/2017	21:32:53	SHW	GJ 12/19/16 email sent to customer- JP
5519	593612	36886628	2/10/2017	21:32:53	SHW	12/14/16 sent invoice to
5520	593612	36886629	2/10/2017	21:32:53	SHW	JG.12/13/16 requested invoice JG."
5521	593612	36925062	2/14/2017	22:36:53	SAB	CALLED #7500 - RECORDING DIAL 204 FOR ACCTG - VM LM
5522	593612	36925066	2/14/2017	22:37:01	SAB	Automatic Debtor status change from NEW to 168.
5523	593612	36946859	2/16/2017	20:40:55	SAB	PER CLT (A CASTILLO) - PER PMT REPORT - PAID
5524	593612	36946860	2/16/2017	20:40:55	SAB	$3,000.00 2/6/17
5525	593612	36946861	2/16/2017	20:40:55	SAB	383471 GREATER FT CHAMBER
5526	593612	36946862	2/16/2017	20:40:55	SAB	P1453081 Check 1003 2/6/2017 3,000.00
5527	593612	36946863	2/16/2017	20:40:55	SAB	0.00 RMI1
5528	593612	36946869	2/16/2017	20:41:22	SAB	(593612-1)Principal Adjustment: MADE PMT $3,000.00 -
5529	593612	36946870	2/16/2017	20:41:22	SAB	ADJ DOWN TO READ $190.34

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 96px;"><col style="width: 80px;"><col style="width: 90px;"><col style="width: 87px;"><col style="width: 78px;"><col style="width: 461px;"><col style="width: 75px;"></colgroup><tbody>
</tbody>

 

[FDibbins]

I would do this with 2 (hidden) helper columns - I used H & I...

	H​	I​	J​
1​	593612	2/16/2017
2​	PER CLT (A CASTILLO) - PER PMT REPORT - PAID	02/16/2017 PER CLT (A CASTILLO) - PER PMT REPORT - PAID
3​	$3,000.00 2/6/17	02/16/2017 PER CLT (A CASTILLO) - PER PMT REPORT - PAID $3,000.00 2/6/17
4​	383471 GREATER FT CHAMBER	02/16/2017 PER CLT (A CASTILLO) - PER PMT REPORT - PAID $3,000.00 2/6/17 383471 GREATER FT CHAMBER
5​	P1453081 Check 1003 2/6/2017 3,000.00	02/16/2017 PER CLT (A CASTILLO) - PER PMT REPORT - PAID $3,000.00 2/6/17 383471 GREATER FT CHAMBER P1453081 Check 1003 2/6/2017 3,000.00
6​	0.00 RMI1	02/16/2017 PER CLT (A CASTILLO) - PER PMT REPORT - PAID $3,000.00 2/6/17 383471 GREATER FT CHAMBER P1453081 Check 1003 2/6/2017 3,000.00 0.00 RMI1
7​	(593612-1)Principal Adjustment: MADE PMT $3,000.00 -	02/16/2017 PER CLT (A CASTILLO) - PER PMT REPORT - PAID $3,000.00 2/6/17 383471 GREATER FT CHAMBER P1453081 Check 1003 2/6/2017 3,000.00 0.00 RMI1 (593612-1)Principal Adjustment: MADE PMT $3,000.00 -
8​	ADJ DOWN TO READ $190.34	02/16/2017 PER CLT (A CASTILLO) - PER PMT REPORT - PAID $3,000.00 2/6/17 383471 GREATER FT CHAMBER P1453081 Check 1003 2/6/2017 3,000.00 0.00 RMI1 (593612-1)Principal Adjustment: MADE PMT $3,000.00 - ADJ DOWN TO READ $190.34

H2=IFERROR(INDEX($F:$F,SMALL(IF(($A$2:$A$15=$H$1)*($C$2:$C$15=$I$1),ROW($A$2:$A$15)),ROWS($A$1:A1))),"")
ARRAY entered using CTRL SHIFT ENTER, not just enter
I2=IF(H2="","",TEXT(I1,"mm/dd/yyyy")&" "&H2)

Then for the summary...
=INDEX(I:I,MATCH("*",$I:$I,-1))

 

[Alex O]

Very Nice...exactly what I was looking for! Thanks

 

[FDibbins]

Good stuff, happy to help

 

[Peter_SSs]

Another couple of options without helper columns just in case they are applicable &/or of interest.

Is it possible to edit my array so all cells that match the most recent date are combined

1. IF you have an Excel version that has the TEXTJOIN function (From MS support: This function is available if you are an Office 365 subscriber and have the latest version of Office installed. It's also available with Excel Online, Excel Mobile, Excel for iOS, and Excel for Android phones and tablets.)

Then assuming
- dates in column C (& are in ascending order as in your sample)
- comments in column F
you could try something like

=TEXTJOIN(" ",TRUE,INDEX(F1:F1000,MATCH(MAX(C1:C1000),C1:C1000,0)):F1000)


2. You could also consider a user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function ConcatDateComments(rDates As Range, dDate As Date, rComments As Range) As String
  Dim aD As Variant, aC As Variant
  Dim i As Long
  
  aD = rDates.Value
  aC = rComments.Value
  For i = 1 To UBound(aD)
    If aD(i, 1) = dDate Then ConcatDateComments = ConcatDateComments & " " & aC(i, 1)
  Next i
  ConcatDateComments = Application.Trim(ConcatDateComments)
End Function

Excel 2010 32 bit
	C	D	E	F	G	H
1						PER CLT (A CASTILLO) - PER PMT REPORT - PAID $3,000.00 2/6/17 383471 GREATER FT CHAMBER P1453081 Check 1003 2/6/2017 3,000.00 0.00 RMI1 (593612-1)Principal Adjustment: MADE PMT $3,000.00 - ADJ DOWN TO READ $190.34
2	10/02/2017	21:32:53	SHW	"01/31/17 left vm JG.1/9/17 Collection email sent by
3	10/02/2017	21:32:53	SHW	GJ
4				12/19/16 email sent to customer- JP
5	10/02/2017	21:32:53	SHW	12/14/16 sent invoice to
6	10/02/2017	21:32:53	SHW	JG.12/13/16 requested invoice JG."
7	14/02/2017	22:36:53	SAB	CALLED #7500 - RECORDING DIAL 204 FOR ACCTG - VM LM
8	14/02/2017	22:37:01	SAB	Automatic Debtor status change from NEW to 168.
9	16/02/2017	20:40:55	SAB	PER CLT (A CASTILLO) - PER PMT REPORT - PAID
10	16/02/2017	20:40:55	SAB	$3,000.00 2/6/17
11	16/02/2017	20:40:55	SAB	383471 GREATER FT CHAMBER
12	16/02/2017	20:40:55	SAB	P1453081 Check 1003 2/6/2017 3,000.00
13	16/02/2017	20:40:55	SAB	0.00 RMI1
14	16/02/2017	20:41:22	SAB	(593612-1)Principal Adjustment: MADE PMT $3,000.00 -
15	16/02/2017	20:41:22	SAB	ADJ DOWN TO READ $190.34
16
17
Concat (2)

Cell Formulas
Range	Formula
H1	=ConcatDateComments(C2:C20,MAX(C2:C20),F2:F20)

 

[FDibbins]

Pete, I have been playing with this some more, trying to make it simpler, only 1 helper and no ARRAY, but hit a stumbling block.

How would you ID the last row of data in this?

	H​
1​	593612
2​
3​
4​
5​
6​
7​
8​
9​	hh,
10​	ii, hh,
11​	jj, ii, hh,
12​	kk, jj, ii, hh,
13​	ll, kk, jj, ii, hh,
14​	mm, ll, kk, jj, ii, hh,
15​	nn, mm, ll, kk, jj, ii, hh,
16​
17​
18​
19​
20​

Keeping in mind that I don't want to change the helper too much (cant sort or adjust the sequence/order at all)
Need to count from row 1 - or at least row 2

 

[Peter_SSs]

How would you ID the last row of data in this?

Is this what you mean?

=LOOKUP(REPT("Z",255),H2:H1000)

 

[FDibbins]

Yup, kinda, just figured it out myself, thanks

Alex, instead of the 2 helpers (1 with an ARRAY), change to this, still using my sample above...
H2=IF(AND(A2=$H$1,C2=$I$1),H1&IF(H1="","",", ")&F2,"")
regular formula, copied down

The for the answer you want...
=INDEX(H:H,LOOKUP(2,1/(H1:H20<>""),ROW(H1:H20)))

 

[FDibbins]

Is this what you mean?

=LOOKUP(REPT("Z",255),H2:H1000)

Actually, I could not get that to work, gave "" as the answer, should be 15

 

[Peter_SSs]

Actually, I could not get that to work, gave "" as the answer, should be 15

That would indicate formulas returning "" below the visible data in post #6, which wasn't obvious from my quick glance.
In any case, I though you wanted to retrieve the last value rather than identify what row it was in.

To get that row, could you use this (if no "" above the first data)?
=MATCH("",H:H,0)-1