Encontrar nombres de familia

Tenemos una serie de nombres con sus apellidos.

Hay nombres que tienen dos apellidos, el ejercicio consiste en encontrar el nombre y apellido, o, apellidos de cada familia, por ejemplo, James Smith, la familia es Smith, pues, debemos de encontrar los nombres y apellidos cuyo ultimo apellido es Smith, cuando un nombre tiene dos apellidos, la familia siempre es el último apellido.

Empecemos…

Lo primer es encontrar la posición del primer espacio, en la celda C2, usamos LET, creamos una variable, la llamamos primer_espacio, usamos la funcion HALLAR que no distingue entre mayúsculas, y, minúsculas, aunque para este caso, no importa porque buscamos el espacio, como argumento texto buscado, ponemos un espacio, como argumento dentro del texto, seleccionamos el rango A2:A21.

Probamos variable.

=LET(primer_espacio;HALLAR(" ";A2:A21);primer_espacio)

Obtenemos una matriz desbordada en vertical con la posición del primer espacio.

Pero, debemos de empezar a extraer después del espacio, por lo que después de la funcion HALLAR, sumamos 1.

=LET(primer_espacio;HALLAR(" ";A2:A21)+1;primer_espacio)

Ahora, vamos a encontrar la posición del segundo espacio, para ello, declaramos otra variable, la llamamos segundo_espacio, volvemos a usar la funcion EXTRAE, el argumento texto buscado, y, dentro del texto es igual que en el EXTRAE anterior, pero, añadimos el argumento numero inicial, es decir, a partir de que posición debe de empezar a buscar el espacio, y, ponemos la variable primer_espacio.

=LET(primer_espacio;HALLAR(" ";A2:A21)+1;segundo_espacio;HALLAR(" ";A2:A21;primer_espacio);segundo_espacio)

Obtenemos la posición del segundo espacio, y, error donde no hay un segundo espacio.

Igual que antes, sumamos 1.

=LET(primer_espacio;HALLAR(" ";A2:A21)+1;segundo_espacio;HALLAR(" ";A2:A21;primer_espacio)+1;segundo_espacio)

Creamos otra variable, la llamamos unión, usamos la funcion SI.ERROR, preguntamos que si la variable segundo_espacio, nos devuelve un error, que nos devuelva la variable primer_espacio.

=LET(primer_espacio;HALLAR(" ";A2:A21)+1;segundo_espacio;HALLAR(" ";A2:A21;primer_espacio)+1;union;SI.ERROR(segundo_espacio;primer_espacio);union)

Ya tenemos la posición donde debemos de empezar a extraer.

Creamos otra variable, la llamamos longitud, usamos la funcion LARGO, como argumento seleccionamos el rango A2:A21, y, sumamos 1.

=LET(primer_espacio;HALLAR(" ";A2:A21)+1;segundo_espacio;HALLAR(" ";A2:A21;primer_espacio)+1;union;SI.ERROR(segundo_espacio;primer_espacio);longitud;LARGO(A2:A21)+1;longitud)

Volvemos a la expresión longitud, y, restamos la variable unión.

=LET(primer_espacio;HALLAR(" ";A2:A21)+1;segundo_espacio;HALLAR(" ";A2:A21;primer_espacio)+1;union;SI.ERROR(segundo_espacio;primer_espacio);longitud;LARGO(A2:A21)+1-union;longitud)

Ya tenemos la posición y caracteres a extraer.

Creamos otra variable, la llamamos extra, usamos la funcion EXTRAE, como argumento texto, seleccionamos el rango A2:A21, como argumento posición inicial, ponemos la variable unión, como argumento numero de caracteres, ponemos la variable longitud.

=LET(primer_espacio;HALLAR(" ";A2:A21)+1;segundo_espacio;HALLAR(" ";A2:A21;primer_espacio)+1;union;SI.ERROR(segundo_espacio;primer_espacio);longitud;LARGO(A2:A21)+1-union;extra;EXTRAE(A2:A21;union;longitud);extra)

Ya hemos extraído el ultimo apellido.

Nos quedamos con los apellidos únicos.

=LET(primer_espacio;HALLAR(" ";A2:A21)+1;segundo_espacio;HALLAR(" ";A2:A21;primer_espacio)+1;union;SI.ERROR(segundo_espacio;primer_espacio);longitud;LARGO(A2:A21)+1-union;extra;UNICOS(EXTRAE(A2:A21;union;longitud));extra)

En la celda D2, volvemos a usar HALLAR, como argumento texto buscado, seleccionamos el primer apellido, como argumento dentro del texto, seleccionamos el rango A2:A21.

Obtenemos la posición de la primera letra del primer apellido, y, error donde no hay coincidencia.

Preguntamos si el resultado es número.

=ESNUMERO(HALLAR(C2;A2:A21))

Obtenemos VERDADERO donde lo es, y, FALSO donde no lo es.

Filtramos el rango A2:A21, siempre que el resultado de ESNUMERO sea VERDADERO.

=FILTRAR(A2:A21;ESNUMERO(HALLAR(C2;A2:A21)))

Ya tenemos los nombres que pertenecen a la familia Smith.

Transponemos.

=TRANSPONER(FILTRAR(A2:A21;ESNUMERO(HALLAR(C2;A2:A21))))

Fijamos A2:A21.

=TRANSPONER(FILTRAR($A$2:$A$21;ESNUMERO(HALLAR(C2;$A$2:$A$21))))

Aceptamos, arrastramos y tenemos los nombres de cada familia.

Vemos que hay familias que tienen un solo nombre.

Esas familias no nos interesan que aparezca en el informe.

En la celda C13, ponemos LET, creamos una variable, usamos BYROW, como argumento array, seleccionamos el rango C2:H10, como argumento funcion ponemos LAMBDA, creamos una variable, usamos CONTARA, y, como argumento ponemos la variable “x”.

=LET(a;BYROW(C2:H10;LAMBDA(x;CONTARA(x)));a)

Obtenemos una matriz desbordada con un recuento de palabras de cada fila.

Debemos tener en cuenta, que en el resultado esta incluido la familia y nombres, cuando nos devuelve un recuento de 2, quiere decir que hay un solo nombre, volvemos a la expresión de la variable “a”, después, de CONTARA ponemos el símbolo de comparación mayor y el número 2.

=LET(a;BYROW(C2:H10;LAMBDA(x;CONTARA(x)>2));a)

Obtenemos VERDADERO donde es mayor a 2, y, FALSO donde n lo es.

Filtramos el rango C2:H10, siempre que el resultado de BYROW sea igual a VERDADERO.

=LET(a;FILTRAR(C2:H10;BYROW(C2:H10;LAMBDA(x;CONTARA(x)>2)));a)

Ya tenemos las familias compuestas por más de un nombre.

Creamos otra variable, usamos MAP, como argumento array, ponemos la variable “a”, como argumento funcion ponemos LAMBDA, creamos una variable, preguntamos si la variable “x” es igual a cero, en ese caso, que nos devuelva blanco, en caso contrario, que devuelva la variable “x”.

=LET(a;FILTRAR(C2:H10;BYROW(C2:H10;LAMBDA(x;CONTARA(x)>2)));b;MAP(a;LAMBDA(x;SI(x=0;"";x)));b)

Obtenemos las familias, nombres, y, blanco donde era cero.

Vamos a usar la funcion UNIRCADENAS, pero como debemos de aplicarla a cada fila, usamos BYROW, como argumento array, es la funcion MAP, como argumento funcion, ponemos LAMBDA, creamos una variable, ponemos UNIRCADENAS, como argumento delimitador, entre comillas dobles, ponemos coma y un espacio, ignoramos celdas vacías, como argumento texto1, es la variable “x”.

=LET(a;FILTRAR(C2:H10;BYROW(C2:H10;LAMBDA(x;CONTARA(x)>2)));b;BYROW(MAP(a;LAMBDA(x;SI(x=0;"";x)));LAMBDA(x;UNIRCADENAS(", ";VERDADERO;x)));b)

Aceptamos, y, ya lo tenemos.

Pero, si queremos que una columna aparezca las familias, y, en otra columna los nombres, en una celda usamos la misma expresión, y, cambiamos el argumento array de FILTRAR por C2:C10.

=LET(a;FILTRAR(C2:C10;BYROW(D2:H10;LAMBDA(x;CONTARA(x)>1)));b;BYROW(MAP(a;LAMBDA(x;SI(x=0;"";x)));LAMBDA(x;UNIRCADENAS(", ";VERDADERO;x)));a)

En la siguiente columna, ponemos la primera expresión, volvemos a cambiar el argumento array de FILTRAR por D2:H10.

=LET(a;FILTRAR(D2:H10;BYROW(C2:H10;LAMBDA(x;CONTARA(x)>2)));b;BYROW(MAP(a;LAMBDA(x;SI(x=0;"";x)));LAMBDA(x;UNIRCADENAS(", ";VERDADERO;x)));b)

Ya lo tenemos.

Miguel Angel Franco