Formula working in 2019 not in 2007

[RAJESH1960]

Hello experts,
I have this Eric W's formula which is working in excel 2019 but showing an error in 2007. Can someone please tell me what can be the solution.?

Book1.xlsx
	A	B	C	D	E	F
1			17/08/2021	aaz/116820829672/a2V/yzavz	#NAME?
2				zzzyz555@ayazz/yzyazazzz
3			17/08/2021	aaz/116820329131/a2V/azzyz	#NAME?
4				aw16889-
5				1@ayzxza/azzyzaW
6			17/08/2021	yzza_zy:yyzyz2116881584	#NAME?
7				3/0031/ yzazaz zyazazaa
8			16/08/2021	yzza_aaa:aayzz21167213	#NAME?
9				021/azyzy
10				zyyavzazaya/yyzy0001435/04
11				13090674
12			16/08/2021	zyaa-	#NAME?
13				zy/116713428622/924626717
14				1/yzazzazy
15
16	Solved by Eric W
17
18				insert column next to description
19				select description column and align text to left
20				remove extra spaces by replace 2 spaces with 1 space
21				Enter formula in Narration Heading row cell E1
22				=IF(C1="","",_xlfn.CONCAT(D1:INDEX(D:D,IFERROR(AGGREGATE(15,6,ROW(C1:C9)/(C2:C10<>""),1),ROW()+10))))
23				range not required to change
Concat rows at each date

Cell Formulas
Range	Formula
E1:E14	E1	=IF(C1="","",_xlfn.CONCAT(D1:INDEX(D:D,IFERROR(_xlfn.AGGREGATE(15,6,ROW(C1:C9)/(C2:C10<>""),1),ROW()+10))))

 

[RAJESH1960]

Of course, I added one extra requirement to insert a space in between the data of each row concatenated. That you have solved too.

 

[RAJESH1960]

You still have to install the code. But there's 2 ways you can use code. One is a sub like JohnnyL showed, it's a procedure that runs when you kick it off. The second way is by using a UDF (User Defined Function). This means you can create your own functions, that you call the same way on the worksheet. That's what I did. Depending on how your spreadsheet works, how you enter the data, etc., you might choose one or the other. This should work fine in 2007.

Oh. I thought the formula will work in 2007 without the code.

 

[RAJESH1960]

I have saved both the codes in my code bank. You never know when I will need like the one Jasonb75's formula came in handy today after 2 years.

 

[RAJESH1960]

Time to grab some sleep. Good Night guys. See you soon.

 

[johnnyL]

=IF(C1="","",_xlfn.CONCAT(D1:INDEX(D:D,IFERROR(_xlfn.AGGREGATE(15,6,ROW(C1:C9)/(C2:C10<>""),1),ROW()+10))))

Your formula indicates 2 problems that will need to be addressed for the 2007 version.

The '_xlfn.' portions of the formula is excel indicating that the function is not supported in that version of Excel.
1) _xlfn.CONCAT
2) _xlfn.AGGREGATE

The Concat is easily fixed with one line of code in a UDF, but because I am not that good with formulas, I am not sure how to address the AGGREGATE function that is not supported.

Perhaps someone will chime in with a way to rewrite the formula without AGGREGATE, or suggest a UDF to use for the AGGREGATE function.

 

[johnnyL]

The closest formula rewrite for the AGGREGATE I can come up with is:
=IF(C2="","",CONCAT(D2:INDEX(D:D,IFERROR(SMALL(IFERROR(ROW(C2:C10)/(C3:C11<>""),""),1),ROW()+10))))

But that is not quite right because as I stated, I am not good with formulas.

 

[Peter_SSs]

If interested, here is another code that also appears to do what you want.

Sub ConcatText()
  Dim rA As Range
  
  With Range("E2:E" & Range("D" & Rows.Count).End(xlUp).Row)
    .Value = .Offset(, -1).Value
    For Each rA In .Offset(, -2).SpecialCells(xlBlanks).Areas
      rA.Cells(0, 3).Value = Join(Application.Transpose(rA.Cells(0, 3).Resize(rA.Rows.Count + 1).Value))
      rA.Offset(, 2).ClearContents
    Next rA
  End With
End Sub

 

[RAJESH1960]

If interested, here is another code that also appears to do what you want.

Sub ConcatText()
  Dim rA As Range
 
  With Range("E2:E" & Range("D" & Rows.Count).End(xlUp).Row)
    .Value = .Offset(, -1).Value
    For Each rA In .Offset(, -2).SpecialCells(xlBlanks).Areas
      rA.Cells(0, 3).Value = Join(Application.Transpose(rA.Cells(0, 3).Resize(rA.Rows.Count + 1).Value))
      rA.Offset(, 2).ClearContents
    Next rA
  End With
End Sub

I am really overwhelmed at the responses received. Thank you everyone.

 

[RAJESH1960]

Your formula indicates 2 problems that will need to be addressed for the 2007 version.

The '_xlfn.' portions of the formula is excel indicating that the function is not supported in that version of Excel.
1) _xlfn.CONCAT
2) _xlfn.AGGREGATE

The Concat is easily fixed with one line of code in a UDF, but because I am not that good with formulas, I am not sure how to address the AGGREGATE function that is not supported.

Perhaps someone will chime in with a way to rewrite the formula without AGGREGATE, or suggest a UDF to use for the AGGREGATE function.

JohnnyL. It is fine man. It is a very rare case where I have to work on 2007 and with the help of your code I can easily solve the problem which I was facing. As for other versions Eric W's formula is very short and very easy to remember. I could use that even without the code.

=partialconcat(C2:D10," ")