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]

This is the link where it was solved earlier
formula to join text in multiple rows at each date.

 

[Eric W]

The reason it doesn't work in Excel 2007 is because CONCAT was added in 2019. Without CONCAT, you can try the formulas Jasonb75 provided in the original thread, which requires a helper column, or it will require VBA.

 

[RAJESH1960]

The reason it doesn't work in Excel 2007 is because CONCAT was added in 2019. Without CONCAT, you can try the formulas Jasonb75 provided in the original thread, which requires a helper column, or it will require VBA.

Thanks Eric W. I did just that to complete my work today. Thanks to Jasonb75 for sharing that formula. It came to my rescue and saved a lot of time. Ain't there any other way to get the result with a single formula which will work in any version of excel.?

 

[Eric W]

Microsoft is adding new functionality to Excel all the time. The last few years have had a lot of new functions added. Sometimes the new functions are just to provide an easier way to do things, but there is a more complicated way using older functions. Sometimes the new functions do things that weren't possible at all. CONCAT is one of those. So I don't believe there's any single-cell way to do that formula in 2007.

 

[RAJESH1960]

Microsoft is adding new functionality to Excel all the time. The last few years have had a lot of new functions added. Sometimes the new functions are just to provide an easier way to do things, but there is a more complicated way using older functions. Sometimes the new functions do things that weren't possible at all. CONCAT is one of those. So I don't believe there's any single-cell way to do that formula in 2007.

Okies. May as well try VBA then.

 

[RAJESH1960]

I am sharing a sample data to join the text in text column at each change in date with the help of a code. At times the data ranges to more than 4000 rows. After not able to get the formula work in 2007 version of excel and as suggested by Eric W, I have to go for a VBA code as I don't have any other option.
My very first query was solved by Rick Rothstein by VBA code for a somewhat similar query.

Sub ConcatToBlankBelow()
'solved by Rick Rothstein 
 Dim Ar As Range
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    If Ar.Count = 1 Then
      Ar(2).Value = Ar.Value
    Else
      Ar(Ar.Count + 1).Value = Join(Application.Transpose(Ar.Value), ", ")
    End If
  Next
End Sub

But I am not able to edit the same. I need your expertise to get the expected result as shown in the image below.

Join text at each change in date.xlsm
	A	B	C	D	E
1			Date	Text	Expected Result
2			01-06-2022	WORK	WORK IS WORSHIP
3				IS
4				WORSHIP
5			01-06-2022	NOTHING	NOTHING IS IMPOSSIBLE
6				IS
7				IMPOSSIBLE
8			02-06-2022	HELLO	HELLO
9			02-06-2022	HAVE	HAVE A NICE DAY
10				A
11				NICE
12				DAY
13			02-06-2022	GOOD	GOOD MORNING
14				MORNING
15			03-06-2022	IMPOSSIBLE	IMPOSSIBLE IS FOR THE UNWILLING
16				IS
17				FOR
18				THE
19				UNWILLING
20			04-06-2022	TRY	TRY AGAIN
21				AGAIN
Join text

 

[johnnyL]

Try this:

Sub ConcatTest()
'
    Dim ArrayRow        As Long, OutputRow          As Long
    Dim BlankRows       As Long, CurrentRow         As Long
    Dim InputArray      As Variant, OutputArray()   As Variant
'
    InputArray = Range("C2:D" & Range("D" & Rows.Count).End(xlUp).Row).Value2       ' Save data from sheet into InputArray
    ReDim OutputArray(1 To UBound(InputArray, 1), 1 To 1)                           ' Set OutputArray to same # of rows as the InputArray
'
    BlankRows = 0                                                                   ' Initialize BlankRows
    OutputRow = 0                                                                   ' Initialize OutputRow
'
    For ArrayRow = 1 To UBound(InputArray, 1)                                       ' Loop through the rows of the InputArray
        If InputArray(ArrayRow, 1) <> vbNullString Then                             '   If Date is not blank then ...
            OutputRow = OutputRow + 1                                               '       Increment OutputRow
'
            CurrentRow = OutputRow + BlankRows                                      '       Get total of OutputRow + BlankRows and save to CurrentRow
            OutputArray(CurrentRow, 1) = InputArray(ArrayRow, 2)                    '       Save Concat word to OutputArray(CurrentRow, 1)
        Else                                                                        '   Else ...
            BlankRows = BlankRows + 1                                               '       Increment BlankRows
            OutputArray(CurrentRow, 1) = OutputArray(CurrentRow, 1) & _
                    " " & InputArray(ArrayRow, 2)                                   '       Append a space & next Concat word to OutputArray(CurrentRow, 1)
        End If
    Next                                                                            ' Loop back
'
    Range("E2:E" & Range("D" & Rows.Count).End(xlUp).Row).Value2 = OutputArray      ' Display Concat word list to sheet
End Sub

 

[RAJESH1960]

JohnnyL. It is perfect. It is working in 2019. Will the same code work in 2007 also.?

 

[johnnyL]

Of Course.