Cómo encontrar valores duplicados según la columna en dos hojas de cálculo y verificar si la fila coincide exactamente

Question

Tengo dos hojas de cálculo excel: Header2018 y Header2019

ambos contienen los mismos encabezados de columna, pero con diferentes entradas de registro. Algunas de estas entradas son iguales y quiero saber cuáles.

Quiero fusionar todos los registros duplicados (según la columna: active_id) en una nueva hoja de cálculo y luego verificar si hay alguna diferencia entre el resto de las columnas.

Encabezado de ejemplo 2018:

asset_id    Name    Country

2          Trent      CAN
3          Alex       CAN
4          Derek      CAN
5          Louis      CAN
6          Teresa     CAN

Cabecera 2019:

asset_id    Name    Country
4          Derek      CAN
5          Louis      USA
1          Kate       CAN
7          Pat        CAN
8          Steven     CAN
9          Tom        CAN

Tabla resultante:

asset_id    Name    Country
4          Derek      CAN
5          Louis      CAN
4          Derek      CAN
5          Louis      USA

Según la tabla resultante, quiero poder encontrar registros donde el activo_id sea el mismo, pero otra columna sea diferente.

¡Gracias de antemano!

Answer 1

Puede utilizar Power Query para obtener el resultado.

1. agregue 2 tablas a Power Query, en el editor de Power Query, vaya a Inicio, agregue 2 consultas:

2. Seleccione la columna active_id- vaya a Transformar- Seleccione Agrupar por- seleccione Avanzado- Agregar agregación- Escriba un nombre para Nueva columna y seleccione Todas las filas:

3. Expanda la tabla y seleccione Nombre y país: columna de recuento de filtros >2, elimine la columna de recuento: cierre y cargue:

Answer 2

Me gustaría sugerir un método, necesita columnas auxiliares y una fórmula de matriz para extraer filas comunes de ambas hojas:

Hoja 1:

• Fórmula en celda A2paraValor de ayuda:

=COUNTIF(Sheet2!$B$2:$B$7, B2)

---

Hoja 2:

• Fórmula en la celda A2:

=COUNTIF(Sheet1!$B$2:$B$6, B2)

NÓTESE BIEN

• Si ambas hojas tienen varios ID duplicados (son más de 2) entonces, en lugar deCONTAR.SI, mejor usa esta fórmula para encontrarDuplicados.

  =IF(ISERROR(MATCH(B2,Sheet2!B$2:B$7,0)),"","Duplicate")

• Y enHoja maestraLos criterios en la celda E59seríanDuplicaren lugar de1.

---

Hoja maestra:

• Una fórmula matricial en la celda G60:

  {=IFERROR(INDEX(Sheet1!$B$2:$D$6, SMALL(IF(COUNTIF($E$59, Sheet1!$A$2:$A$6), ROW(Sheet1!$B$2:$D$6)-MIN(ROW(Sheet1!$B$2:$D$6))+1), ROW(A1)), COLUMN(A1)),IFERROR(INDEX(Sheet2!$B$2:$D$7, SMALL(IF(COUNTIF($E$59, Sheet2!$A$2:$A$7), ROW(Sheet2!$B$2:$D$7)-MIN(ROW(Sheet2!$B$2:$D$7))+1), ABS(ROW(A1)-2)), COLUMN(A1)),""))}
  

• Terminar la fórmula conCtrl+Mayús+Entrar, llene hacia la derecha y luego hacia abajo.

• La fórmula In Array ABS(ROW(A1)-2))se ha utilizado para ajustarNumero de filapara Sheet 2a 1.

Puede ajustar las referencias de celda en la Fórmula según sea necesario.