Contando secuencias en un rango de Excel.

Hace un par de días un usuario del blog me contactó preguntándome por la manera de construir una secuencia 'especial' sobre un rango de celdas:

Lo que yo requiero es contar la secuencia de las columnas pero de una forma simplificada La columna A: 3A, 1I, 2A, 1G, 1E, 1B, 1A, 1E, 4A. … y asi por cada columna, la idea es como un resumen, pero no solo contando sino conservando el orden

Partiremos de la siguiente serie de datos (nos centraremos en la columna A):

El asunto está claro, necesitamos construir, de alguna manera, el conteo de elementos continuos en el rango a evaluar. Es decir,a cada salto por diferencias entre el elemento anterior y siguiente, mostraremos el número de repeticiones habidas. Y lo haremos sin emplear macros, mediante fórmulas.

Para hacerlo sencillo de explicar construiremos dos columnas auxiliares que nos apoyarán en la obtención de lo buscado.
En nuestra Aux1 dispondremos, para nuestro ejemplo, la siguiente fórmula en la celda F2:
=SI(FILAS($A$1:A1)=1;1;SI(A2=A1;F1+1;1))
con ella conseguimos que para el primer valor del rango aparezca un 1:
SI(FILAS($A$1:A1)=1;1;...
y para el resto de filas aplica lo importante
...;SI(A2=A1;F1+1;1)
es decir, cuando haya un cambio de elemento, asignamos de nuevo el valor 1, y en caso que sea el mismo elemento acumulamos +1 al valor anterior.
Vemos el resultado en la imagen:

En este primer paso hemos conseguido tener numerada las series continuas que aparezcan en nuestro rango (columna A), preparando el terreno para nuestra segunda Axuliar, donde en G2 insertaremos la fórmula:
=SI(A2=A3;"";A2)
y arrastrando hacia abajo:

Lo conseguido con este segundo paso es claro, mostrar sólo valores en aquellos casos en los que se produce un salto o cambio de elemento en la secuencia a estudiar.

El último paso es sencillo, concatenamos los elementos de nuestras dos auxilares... en la celda H2 insertamos:
=SI(G2="";"";F2&G2)
pero sólo en los casos que nos interesa, es decir, en las filas que se produce cambio.

Realmente el trabjo está conseguido, podemos ver para nuestra columna A evaluada el conteo de elementos, por intervalos de salto, manteniendo el orden.

Sólo nos queda un paso final, tener esa secuencia en una sola celda.
Para ello seguiremos las siguientes instrucciones:
1- situarnos en la celda donde queramos ver el resultado final, por ejemplo, celda H18
2- teclea =H2:H16&" " en la barra de formulas
3- aprieta la tecla F9 (lo que convertirá la 'fórmula' en una matriz).
4- borra las llaves que se pusieron al principio y al final, que representaba la matriz.
5- escribe =ESPACIOS(CONCATENAR( al principio de la barra de formulas y )) al final de la barra de formulas
6- presiona Enter
Vemos el proceso en este corto video:

Aunque algo manual y estático, este pequeño truco nos evita emplear macros o funciones UDF para conseguir el resultado.
El truco consiste en concatenar, al emplear =H2:H16&" ", el elemento seguido de un espacio, lo que genera más de un caracter espacio en aquellos casos que el elemento de H2:H16 esté sin valor; que luego corregimos con al función ESPACIOS que elimina espacios repetidos, dejando únicamente un espacio entre elemento resultante.

Si quisieramos tener separado por coma los diferentes elementos, sería suficiente anidar toda nuestra fórmula en una función SUSTITUIR:
=SUSTITUIR(H18;" ";",")

Para automatizar más la tarea (olvidándonos de la parte manual expuesta), podríamos construir una sencilla función VBA personalizada (UDF) con el siguiente código:

Function CONCATENARCELDAS(Rango As Excel.Range) As String
Application.Volatile
'Bucle para recorrer todas las celdas del rango
For Each celda In Rango.Cells
    'Si la celda está NO vacía, entonces la concatenamos al anterior
    If celda.Value <> "" Then
        concatenado = concatenado & ", " & celda.Value
    End If
Next celda

'Se elimina el ; y espacio inicial
concatenado = Right(concatenado, Len(concatenado) - 2)

CONCATENARCELDAS = concatenado

End Function