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]

hmm I must be doing something wrong, still not working.

This is the range (could be longer, just kept the sample small...

	F​	G​	H​
1​	F	G	593612
2​	aa
3​	bb
4​	cc
5​	dd
6​	ee
7​	ff
8​	gg
9​	hh		hh
10​	ii		hh, ii
11​	jj		hh, ii, jj
12​	kk		hh, ii, jj, kk
13​	ll		hh, ii, jj, kk, ll
14​	mm		hh, ii, jj, kk, ll, mm
15​	nn		hh, ii, jj, kk, ll, mm, nn
16​
17​
18​
19​
20​

F contains the data
H pulls out based on a code and a date (doesn't really matter what they are, it is only working on the results)...
H2=IF(AND(A2=$H$1,C2=$I$1),H1&IF(H1="","",", ")&F2,"")
copied down

So, based on that formula, there will be a bunch of "" at the start, then some "data", then potentially some more ""

 

[Alex O]

The data is much longer (about 5k rows - 250+ clients). I'm still playing with Pete's suggestion. I can send the actual sheet if you think it would help?

 

[FDibbins]

No, My suggestion from post #8 will work on that range, you just need to adjust the ranges as needed...

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:H10000<>""),ROW(H1:H10000)))

 

[Peter_SSs]

I'm still playing with Pete's suggestion.

As a matter of interest, TEXTJOIN or UDF?