Forum Discussion

Jyggalag77's avatar
Jyggalag77
Copper Contributor
Aug 11, 2022
Solved

Conditionally merge cells based on number?

Hi all, I want my cell to be merged like this (see attached photo). What defines my data is that there is always an age, so I would like to have, for example, a formula where once a number en...
excel
Formulas and Functions
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Aug 11, 2022

    Jyggalag77 

    Sub merge()

Dim i As Long
Dim x As Variant
Dim result As String
Dim k As Long
Dim m As Long

Range("B:B").Clear

m = Range("A" & Rows.Count).End(xlUp).Row
k = 3

For i = 3 To m

x = Right(Cells(i, 1), 1)
If IsNumeric(x) Then

Cells(k, 2).Value = Trim(result & " " & Cells(i, 1).Value)
k = k + 1
result = ""

Else

result = Trim(result & " " & Cells(i, 1).Value)

End If

Next i

End Sub

    Maybe with this code. In the attached file you can click the button in cell D2 to run the macro.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 11, 2022

Jyggalag77 This does something similar with an Excel 365 formula.

WorksheetFormula
= LET(
   consolidated, REDUCE("", data, JoinToLastλ),
   DROP(consolidated,-1))

JoinToLastλ 
= LAMBDA(list, term,
    LET(
        initialLine?, ROWS(list) = 1,
        endBlock?,    ISNUMBER(VALUE(RIGHT(term, 1))),
        priorTerms,   IF(initialLine?, "None", DROP(list, -1)),
        finalTerm,    TEXTJOIN(",",,TAKE(list, -1), term),
        updatedList,  IF(initialLine?, finalTerm, VSTACK(priorTerms, finalTerm)),
        IF(endBlock?, VSTACK(updatedList, ""), updatedList)
    )
);

Not that it is going to look any more familiar than the VBA!

What it does is to join each new term to the final element of an array but then appends a blank element if the term ends with a number.  

 

  • Jyggalag77's avatar
    Jyggalag77
    Copper Contributor
    Aug 11, 2022
    Thank you so much Peter! I will make sure to take a look at this as well 🙂

Resources