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
  )
)
Lorenzo's avatar
Lorenzo
Silver Contributor
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
  )
)

Resources