Forum Discussion

ishaqib's avatar
ishaqib
Copper Contributor
Dec 27, 2022
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
admin
BI & Data Analysis
Charting
developer
excel
Excel for web
Excel on Mac
Excel on mobile
Formulas and Functions
Macros and VBA
  • Lorenzo's avatar
    Lorenzo
    Dec 27, 2022

    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
  )
)
HansVogelaar's avatar
HansVogelaar
MVP
Dec 27, 2022

ishaqib 

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

Resources