Excel, how to dynamically modify column cells until next different value-CodePudding

For example:

I want it to become:

This clearly has to be done dynamically.

CodePudding user response：

In Office 365 you could create the required result in a different column/sheet with this formula: =A1:A11&IF(COUNTIF(OFFSET(A1,,,SEQUENCE(COUNTA(A1:A11),),),A1:A11)-1=0,"","_"&COUNTIF(OFFSET(A1,,,SEQUENCE(COUNTA(A1:A11),),),A1:A11)-1)

Or using LET:

=LET(data,A1:A11, 
     countif,COUNTIF(OFFSET(data,,,SEQUENCE(COUNTA(data))),data)-1,
     data&IF(countif=0,"","_"&countif))

If you want the data replaced with the amended data you need a VBA solution