Reordenación de listados en horizontal.

Supondremos que disponemos de un listado de información cruzada, con datos alternos en nuestras filas, del cual pretendemos obtener a nuestra discrección un nuevo listado ordenado en vertical.
Para ello trabajaremos con distintas herramientas ya conocidas por los habituales del blog, entre otras asignaremos nombres a diferentes rangos, configuraremos una celda validada, y haremos uso de las funciones INDICE, JERARQUIA e INDIRECTO entre otras.
En el ejemplo que proponemos tenemos un listado de clientes de un club polideportivo, con información para cada socio de los deportes que practica.
Esta es nuestra tabla de información cruzada:

Nuestro primer trabajo consistirá en construir una tabla auxiliar aneja a la original, donde por medio de una función SI CONDICIONAL, convertiremos las marcas de los deportes de cada socio en un texto; mediante la función
=SI(C2<>"";C$1;"")

El paso siguiente de nuestro ejercicio será asignar nombres a los rangos recién creados (de nuestro hoja de cálculo 'Horizontal')
Aaa_Aaaaa =Horizontal!$H$2:$L$2
Bbbb_Bbbbb =Horizontal!$H$3:$L$3
Cccc_Ccccc =Horizontal!$H$4:$L$4
Dddd_Ddddd =Horizontal!$H$5:$L$5
Eeee_Eeeee =Horizontal!$H$6:$L$6
Ffff_Ffffff =Horizontal!$H$7:$L$7
Ggggg_Gggggg =Horizontal!$H$8:$L$8
Hhhhh_Hhhhh =Horizontal!$H$9:$L$9
Ya que los nombres de rangos no pueden tener espacios en blanco, he añadido un guión bajo en su lugar; puesto que para posteriores pasos me interesa mantener lo máximo posible la estructura original. Ver cómo realizar este paso en asignar nombres a rangos.

Otra etapa en nuestro proceso será generar una celda validada tipo Lista, con el rango de los nombres de los socios:

Con esta validación no habremos terminado, ya que se trata de relacionar los Nombres y Apellido de nuestros Socios, con los Nombres definidos (que tienen un guión bajo entre Nombre y Apellido); por este motivo en una celda abajo, insertaremos la siguiente función
=SUSTITUIR(N2;" ";"_")
donde N2 es la celda con nuestra validación.

Ya casi terminamos, un poco de paciencia...

Construimos unas columnas auxiliares que nos van a permitir ordenar esos deportes practicados por nuestro Socio elegido al desplegar nuestra celda N2. La primera columna auxiliar listará los 'deportes' del socio en cuestión, empleando el nombre asignado para cada socio. Con la función INDIRECTO anidada dentro de la función INDICE, con lo que convertimos un valor en un Rango:

En otra de las columnas auxiliares, con la función
=SI(CELDA("contenido";P2)="";0;FILA())
damos un valor a cada deporte, según tenga valor esa fila o no.

Hemos conseguido que para cada vez que haya un Deporte nos asigne un número, en concreto el número de fila en que se encuentra. Da igual qué fila sea, lo importante es tener un valor numérico con el que ordenar posteriormente.
En la última de las columnas auxiliares aplicamos sobre la anterior la función
=JERARQUIA(Q2;$Q$2:$Q$6)
con lo que conseguimos una ordenación de nuestros deportes.

Acabamos por fin...
Construimos nuestro listado en vertical, ya ordenados, de nuestro Socio elegido.

Con la función
=INDICE($P$2:$P$6;COINCIDIR(FILA()-5;$R$2:$R$6;0))
hemos conseguido lo que deseábamos; analicémosla:

• con COINCIDIR(FILA()-5;$R$2:$R$6;0) obtenemos el número de columna que emplearemos en la función INDICE


• con la función INDICE tomando como matriz la primera de nuestras columnas auxiliares, i.e., los Deportes listados; al asignarle la coincidencia anterior, tenemos el valor deseado.

Vemos que con cuatro pasos hemos sido capaces de convertir de manera automatizada unos listados desordenados en horizontal en un listado vertical ordenado.