☰

Rellenar una columna a partir de otra columna para cada fila de un grupo

tag-icon • tag-icon microsoft-excel
Rellenar una columna a partir de otra columna para cada fila de un grupo

Estoy en un ejercicio de migración de datos y tengo una hoja de datos de más de 100.000 filas, por lo que necesito una solución más allá de una actualización manual.

Por el bien de esta publicación, simplificaré el problema: tengo una hoja con tres columnas (A, B y C) con encabezados [tipo de datos] de la siguiente manera: Nombre [texto], ID [número], CreateDate [fecha ]. Los datos se han ordenado en la columna A para resaltar deliberadamente los valores duplicados en esa columna. Las identificaciones son únicas; las fechas sonnoúnico.

En el ejemplo, hay nueve filas que se pueden "agrupar" en tres grupos en virtud de los valores duplicados en la columna A. Entonces, para las filas 2 y 3, el valor en la columna A es abc, para las filas 4, 5 y 6, el valor en la columna A. es def, y para las filas 7-10 es ghi.

La primera tarea es determinar la última CreateDate en cada uno de los 'grupos de filas'. Entonces, en este ejemplo, sería el 11/05/1999 para las filas 2 y 3, el 12/03/2001 para las filas 4 a 6 y el 11/05/1999 para las filas 7 a 10. Hago esto usando la fórmula matricial {=MAX(IF(A2=$A:$A,$C:$C))} en la columna D.

La próxima tarea está resultando difícil. Ahora que he determinado la última fecha para cada grupo de filas, quiero poner el ID que corresponde a esa LatestDate en la columna E (en cada fila del grupo de filas), de modo que el resultado se vea como la Columna E en el siguiente ejemplo. Pero necesito hacerlo con una fórmula/función, no manualmente. Esto es para un proyecto de migración, por lo que no tiene por qué ser bonito.

Poner el ID correspondiente en la columna E de la misma fila es fácil (una declaración IF) (consulte las filas 3, 6 y 9), pero no pretendo que esa sea la solución. Pero no puedo encontrar la manera de completar las otras filas de cada grupo con ese mismo ID; en mi ejemplo adjunto, serían las filas 2, 4 y 5, 7 y 8 y 10. No puedo simplemente hacer una búsqueda. en la columna C porque esos valores no son únicos. Después de intentos inútiles con varias fórmulas, estoy perdido. Supongo que debe depender de la columna C (los valores coincidentes) y funciones como INDEXy  MATCH... o tal vez VBA.

     +------+------+------------+------------+-----------------+
     |  A   |   B  |     C      |     D      |        E        |
+----+------+------+------------+------------+-----------------+
|  1 | Name |  ID  | CreateDate | LatestDate | CorrespondingID |
|  2 | abc  |   1  | 4/12/1998  | 5/11/1999  |         2       |
|  3 | abc  |   2  | 5/11/1999  | 5/11/1999  |         2       |
|  4 | def  |   3  | 1/12/1999  | 3/12/2001  |         5       |
|  5 | def  |   4  | 5/11/1999  | 3/12/2001  |         5       |
|  6 | def  |   5  | 3/12/2001  | 3/12/2001  |         5       |
|  7 | ghi  |  17  | 1/17/1999  | 5/11/1999  |        55       |
|  8 | ghi  |  42  | 2/4/1999   | 5/11/1999  |        55       |
|  9 | ghi  |  55  | 5/11/1999  | 5/11/1999  |        55       |
| 10 | ghi  |  83  | 3/28/1999  | 5/11/1999  |        55       |
+----+------+------+------------+------------+-----------------+

(Hay un ejemplo más breve disponible como imagen).

Respuesta1

mi referencia esaquí. TLDR: utilice la versión sin matriz de la fórmula de coincidencia de índice.

En E2 poner:

=INDEX(B:B,MATCH(1,INDEX((A2=A:A)*(D2=C:C),0,1),0))

Idea: el índice interno () genera una lista 0 y 1 que coincide con los criterios de nombre y fecha. luego el index() externo se usa para 'cargar' el nombre de A:A .

Respuesta2

Esta solución utiliza el método de coincidencia de compensación. Usar una coincidencia para encontrar la fecha y luego compensar según la fecha para encontrar la identificación correspondiente:

=DESPLAZAMIENTO(C2,COINCIDENCIA(D2,C2:C,0)-1,-1)

Suelte esto en la celda E2 y luego arrástrelo hacia abajo a lo largo de E

Respuesta3

Para una fila determinada (por ejemplo, Fila 2), desea encontrar el ID (Columna  B) de una Fila norte(es decir, Celda  ) donde el nombre ( ) es igual al nombre de la fila actual ( ) y la Fecha de creación ( ) es igual a la Fecha más reciente de la fila actual ( ). Fila BnAnA2CnD2nortees único porque los ID son únicos, y por eso "el"La fila es la fila máxima. Una respuesta lógica sería una extensión menor de su fórmula para Columna  D:

=MAX(IF(AND(A2=$A:$A,D2=$C:$C), $B:$B))

Desafortunadamente, ANDno parece funcionar en fórmulas matriciales. Entonces usamos un truco estándar: TRUE = 1 (o cualquier otro que no sea cero) y  FALSE = 0, por lo que podemos simular ANDcon la multiplicación ( AND(TRUE,TRUE) =  TRUE igual que 1 × 1 = 1,  =  igual que 0 × AND(FALSE,anything)FALSEcualquier cosa = 0). Entonces cambiamos lo anterior a

=MAX(IF((A2=$A:$A)*(D2=$C:$C), $B:$B))

(ingresado como una fórmula matricial, con Ctrl+ Shift+ Enter, por supuesto):

Tenga en cuenta que esto funciona con Fechas de creación no únicas. Incluso funciona si las Fechas de creación no están en el mismo orden que los ID, como en el ejemplo anterior (donde los datos están ordenados por columnas  Ay  B).

información relacionada