¿Cómo comparo dos columnas para valores coincidentes y no coincidentes y los obtengo en el formato que quiero?

Question

¿Cómo encuentro valores coincidentes y no coincidentes entre dos columnas y los formateo de la siguiente manera?

Aporte:

| expected | match | forward | backward | actual |
|----------|-------|---------|----------|--------|
| a        |       |         |          | b      |
| b        |       |         |          | c      |
| c        |       |         |          | r      |
| d        |       |         |          | s      |
| e        |       |         |          |        |

Producción:

| expected | match | forward | backward | actual |
|----------|-------|---------|----------|--------|
| a        | b     | a       | r        | b      |
| b        | c     | d       | s        | c      |
| c        |       | e       |          | r      |
| d        |       |         |          | s      |
| e        |       |         |          |        |

forward- presente en expectedpero no en actual( SQL left outer join)

backward- presente en actualpero no en expected( SQL right outer join)

expectedes lo que obtengo de una SQLconsulta. Tengo muchos escenarios en los que no tengo la actualcolumna en el RDBMS, por lo que tendría que usar Excel para comparar. Puedo compararlo normalmente, VLOOKUPpero lleva mucho tiempo y además no me da el formato que quiero. Me gustaría una solución que se pueda hacer.en tono rimbombantecon el formato anterior.

Estoy abierto a sugerencias.

Answer 1

Suponiendo que su información esté presentada como se muestra arriba, puede utilizar las siguientes tres fórmulas en C3, D3 y E3 respectivamente:

C3
=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($B$3:$B$7)/(COUNTIF($B$3:$B$7,$F$3:$F$7)>0),ROW(A1))),"")

D3
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$3:$B$7)/(COUNTIF($C$3:$C$7,$B$3:$B$7)=0),ROW(A1))),"")

E3
=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($F$3:$F$7)/((COUNTIF($C$3:$C$7,$F$3:$F$7)=0)*($F$3:$F$7<>"")),ROW(A1))),"")

copie las fórmulas hasta la fila 7 y terminará con lo siguiente:

Nota: AGGREGATErealiza cálculos tipo matriz para la función 15. Como resultado, no utilice la referencia de columna completa dentro de la AGGREGATEfunción. Restrinjalo a estar cerca de su conjunto de datos. De lo contrario, puedes atascar tu computadora con una enorme cantidad de cálculos en celdas en blanco.

Answer 2

Para Power Query su transformación clave seríaFusionar consultas. Es similar a una unión SQL y utiliza terminología similar. El primer paso sería crear consultas separadas para elesperadoyactualcolumnas. Para elfósforolist Yo crearía una consulta utilizando el tipo de unión = interno. Para eladelantelista, crearía otra consulta usando Join Type = Left Anti, luego una más parahacia atrásusando Anti-Derecha.

Reunirlo todo exactamente como lo presentó sería un poco complicado (si eso es realmente necesario), pero imagino que podría agregar un índice a cada consulta y usar combinaciones de combinación (tipo de unión = exterior izquierdo) en las columnas del índice para obtener un único resultado de cada consulta en cada fila.