Forum Discussion
Solved
Concat column B text so data will look like column G And delete blank cells
Hello everyone, i am trying so hard to solve this problem i used concat function textjoin but didn't get the exact answer what i want, Please help me to get rid our of this, Thanks in Advance
Hi ishaqib
A dynamic array alternative if you run 365:
in D4:
=LET( HstackDateString, LAMBDA(Dates,Strings,date, HSTACK(date, TEXTJOIN(" ",,FILTER(Strings,Dates=date))) ), Dates, SCAN(,CHOOSECOLS(Data,1), LAMBDA(seed,x, IF(x <> "", x, seed)) ), DROP( REDUCE("",UNIQUE(Dates), LAMBDA(seed,date, VSTACK(seed,HstackDateString(Dates,CHOOSECOLS(Data,2),date))) ), 1 ) )
3 Replies
Hi ishaqib
A dynamic array alternative if you run 365:
in D4:
=LET( HstackDateString, LAMBDA(Dates,Strings,date, HSTACK(date, TEXTJOIN(" ",,FILTER(Strings,Dates=date))) ), Dates, SCAN(,CHOOSECOLS(Data,1), LAMBDA(seed,x, IF(x <> "", x, seed)) ), DROP( REDUCE("",UNIQUE(Dates), LAMBDA(seed,date, VSTACK(seed,HstackDateString(Dates,CHOOSECOLS(Data,2),date))) ), 1 ) )An alternative could be Power Query. In the attached file you can enter data in the blue dynamic table. Then you can click in any cell of the green table with the mouse and right-click and then select refresh.
Assuming that the "real" data start in row 1, run the following macro:
Sub Transform() Dim r As Long Dim m As Long Dim arr() As Variant Dim rng As Range Application.ScreenUpdating = False m = Range("B" & Rows.Count).End(xlUp).Row arr = Range("A1:B" & m).Value For r = m - 1 To 1 Step -1 If arr(r + 1, 1) = "" Then arr(r, 2) = arr(r, 2) & " " & arr(r + 1, 2) If rng Is Nothing Then Set rng = Range("A" & r + 1) Else Set rng = Union(Range("A" & r + 1), rng) End If End If Next r Range("A1:B" & m).Value = arr rng.EntireRow.Delete Application.ScreenUpdating = True End Sub
