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]

Ok. Will test it in the office once again tomorrow. Thanks man.

 

[johnnyL]

Glad we could help.

 

[johnnyL]

What should the correct responses be for data in post #1 using the formula?

 

[RAJESH1960]

It gives the same result as your code but works only in 2019 version. In 2007 I was getting an error.

 

[Eric W]

If you'd rather use a UDF instead of a sub, try this:

Public Function PartialConcat(target As Range, delim As String) As String
Dim i As Long, MyData As Variant

    Application.Volatile
    If target.Columns.Count <> 2 Then
        PartialConcat = "Incompatible range dimensions"
        Exit Function
    End If
    
    MyData = target.Value
    If MyData(1, 1) = "" Then Exit Function
    PartialConcat = MyData(1, 2)
    For i = 2 To UBound(MyData)
        If MyData(i, 1) <> "" Then Exit Function
        PartialConcat = PartialConcat & delim & MyData(i, 2)
    Next i

End Function

Book3
	A	B	C	D	E
1			Date	Text	Expected Result
2			6/1/2022	WORK	WORK IS WORSHIP
3				IS
4				WORSHIP
5			6/1/2022	NOTHING	NOTHING IS IMPOSSIBLE
6				IS
7				IMPOSSIBLE
8			6/2/2022	HELLO	HELLO
9			6/2/2022	HAVE	HAVE A NICE DAY
10				A
11				NICE
12				DAY
13			6/2/2022	GOOD	GOOD MORNING
14				MORNING
15			6/3/2022	IMPOSSIBLE	IMPOSSIBLE IS FOR THE UNWILLING
16				IS
17				FOR
18				THE
19				UNWILLING
20			6/4/2022	TRY	TRY AGAIN
21				AGAIN
Sheet14

Cell Formulas
Range	Formula
E2:E21	E2	=partialconcat(C2:D10," ")

Note that if you don't want the space between values, change the formula to:

=partialconcat(C2:D10,"")

The second parameter is the delimiter.

 

[RAJESH1960]

=partialconcat(C2:D10,"")

I hope it works in 2007 also. Will update once I check it out tomorrow. As you suggested to go with a code, I gave up to use a formula as you said concat was added in 2019. If the formula works then I will go with it.
Thanks Eric W.

 

[johnnyL]

It gives the same result as your code but works only in 2019 version. In 2007 I was getting an error.

I am asking because when I plug in your formula, the result is only the first word of each result.

 

[RAJESH1960]

I am asking because when I plug in your formula, the result is only the first word of each result.

You have to insert this formula in the first cell E1 and drag it down to get the correct result.

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

 

[Eric W]

I hope it works in 2007 also. Will update once I check it out tomorrow. As you suggested to go with a code, I gave up to use a formula as you said concat was added in 2019. If the formula works then I will go with it.
Thanks Eric W.

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.

 

[johnnyL]

The issue is I am using 2016 & that has Concatenate, not concat which is different.