Crear modelo por ID planeta river y subject

Tenemos los siguientes datos, donde tenemos un ID, y, dato correspondiente a cada ID.

Tenemos otras dos columnas, donde tenemos una columna llamada datos y otra llamada categoría.

Debemos de crear un modelo, donde en horizontal deben de aparecer los encabezados.

En la primera columna los ID únicos.

En la columna planeta, debe de aparecer los planetas que corresponde con cada ID, en la tercera columna, los river que pertenecen a cada ID, igual para la columna subject.

En la celda G1, usamos la funcion APILARH, como argumento matriz1, seleccionamos la celda A1, como argumento matriz2, usamos la función ENFILA, como argumento matriz ponemos la funcion UNICOS, como argumento matriz seleccionamos el rango E2:E26.

=APILARH(A1;ENFILA(UNICOS(E2:E26)))

Ya tenemos el encabezado.

En la celda G2, nos traemos los valores únicos del rango A2:A16.

=UNICOS(A2:A16)

En la celda H2, usamos LET, creamos una variable, seleccionamos el rango A2:B16, probamos variable.

=LET(a;A2:B16;a)

Obtenemos una matriz desbordada de dos columnas, en la primera columna tenemos los ID, y, en la segunda columna, los datos.

Creamos otra variable, usamos la funcion BUSCARX, como argumento valor buscado, usamos la función TOMAR, como argumento matriz, ponemos la variable “a”, omitimos el argumento filas, como argumento columnas, ponemos -1, es decir, estamos seleccionando la última columna, como argumento matriz de búsqueda, seleccionamos el rango D2:D26, como argumento matriz devuelta, seleccionamos el rango E2:E26, como argumento si no se encuentra, ponemos un espacio.

=LET(a;A2:B16;b;BUSCARX(TOMAR(a;;-1);D2:D26;E2:E26;"");b)

Obtenemos la categoría a que pertenece cada dato.

Usamos la funcion APILARH, como argumento matriz1, ponemos la variable “a”, como argumento matriz2 es la funcion BUSCARX.

=LET(a;A2:B16;b;APILARH(a;BUSCARX(TOMAR(a;;-1);D2:D26;E2:E26;""));b)

Tenemos una matriz desbordada de tres columnas, en la primera columna, tenemos el ID que corresponde con cada dato de la segunda columna, y, en la tercera columna, tenemos la categoría que pertenece a cada dato.

Creamos otra variable, usamos la funcion FILTRAR, como argumento array, ponemos la variable “b”, como argumento Include, usamos la funcion TOMAR, como argumento matriz, ponemos la variable “b”, omitimos el argumento filas, como argumento columnas, ponemos -1, y, comparamos con diferente a blanco.

=LET(a;A2:B16;b;APILARH(a;BUSCARX(TOMAR(a;;-1);D2:D26;E2:E26;""));c;FILTRAR(b;TOMAR(b;;-1)<>"");c)

Ya no tenemos blancos.

Creamos otra variable, ponemos el condicional SI, como argumento prueba lógica, abrimos un paréntesis, usamos la función TOMAR, como argumento matriz, ponemos la variable “c”, omitimos el argumento filas, como argumento columnas, ponemos 1, igualamos al valor de G2, cerramos paréntesis, ponemos el símbolo de asterisco, abrimos otro paréntesis, usamos la función INDICE, como argumento matriz, ponemos la variable “c”, omitimos el argumento numero de fila, como argumento numero de columna, ponemos 3, igualamos a H1,cerramos paréntesis.

=LET(a;A2:B16;b;APILARH(a;BUSCARX(TOMAR(a;;-1);D2:D26;E2:E26;""));c;FILTRAR(b;TOMAR(b;;-1)<>"");d;SI((TOMAR(c;;1)=G2)*(INDICE(c;;3)=H1)

Como argumento valor si verdadero, usamos INDICE, como argumento matriz, ponemos la variable “c”, omitimos el argumento numero de fila, como argumento número de columna, ponemos 2, como argumento valor si falso, ponemos un texto en blanco.

=LET(a;A2:B16;b;APILARH(a;BUSCARX(TOMAR(a;;-1);D2:D26;E2:E26;""));c;FILTRAR(b;TOMAR(b;;-1)<>"");d;SI((TOMAR(c;;1)=G2)*(INDICE(c;;3)=H1);INDICE(c;;2);"");d)

Tenemos una matriz desbordada en vertical con la categoría donde hay coincidencia, y, blanco donde no la hay.

Usamos la funcion UNIRCADENAS, como argumento delimitador, entre comillas dobles, ponemos coma, ignoramos celdas vacías, como argumento texto1, es el condicional SI.

=LET(a;A2:B16;b;APILARH(a;BUSCARX(TOMAR(a;;-1);D2:D26;E2:E26;""));c;FILTRAR(b;TOMAR(b;;-1)<>"");d;UNIRCADENAS(",";VERDADERO;SI((TOMAR(c;;1)=G2)*(INDICE(c;;3)=H1);INDICE(c;;2);""));d)

Fijamos las siguientes referencias.

=LET(a;$A$2:$B$16;b;APILARH(a;BUSCARX(TOMAR(a;;-1);$D$2:$D$26;$E$2:$E$26;""));c;FILTRAR(b;TOMAR(b;;-1)<>"");d;UNIRCADENAS(",";VERDADERO;SI((TOMAR(c;;1)=$G2)*(INDICE(c;;3)=H$1);INDICE(c;;2);""));d)

Estando en la celda H2, pulsamos CTRL más C, seleccionamos las celdas donde vamos a copiar, pulsamos CTRL mas V, y, ya lo tenemos.

Miguel Angel Franco