Como posso comparar duas colunas para valores correspondentes e não correspondentes e obtê-los no formato desejado?

Question

Como encontro valores correspondentes e não correspondentes entre duas colunas e formato-os da maneira abaixo?

Entrada:

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

Saída:

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

forward- presente em expectedmas não em actual( SQL left outer join)

backward- presente em actualmas não em expected( SQL right outer join)

expectedé o que recebo de uma SQLconsulta. Tenho muitos cenários em que não tenho a actualcoluna no RDBMS, então teria que usar o Excel para comparar. Posso comparar normalmente usando VLOOKUPmas é demorado e também não dá o formato que desejo. Gostaria de uma solução que possa ser feitaimportantecom formato como acima.

Estou aberto a sugestões.

Answer 1

Supondo que suas informações estejam dispostas como acima, você pode usar as três fórmulas a seguir em C3, D3 e 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 as fórmulas até a linha 7 e você terá o seguinte:

Nota: AGGREGATEexecuta cálculos semelhantes a array para a função 15. Como resultado, não use referência de coluna completa dentro da AGGREGATEfunção. Restrinja-o a estar próximo do seu conjunto de dados. Caso contrário, você pode sobrecarregar seu computador com uma enorme quantidade de cálculos em células em branco.

Answer 2

Para o Power Query, sua transformação principal seriaMesclar consultas. É semelhante a uma junção SQL e usa terminologia semelhante. O primeiro passo seria criar consultas separadas para oesperadoerealcolunas. Para ocorresponderlist eu construiria uma consulta usando Join Type = Inner. Para oavançarlist eu construiria outra consulta usando Join Type = Left Anti, depois mais uma parapara trásusando o Anti direito.

Reunir tudo exatamente como você apresentou seria um pouco complicado (se isso for realmente necessário), mas imagino que você poderia adicionar um índice a cada consulta e usar Merge Joins (Join Type = Left Outer) nas colunas do Índice para obter um único resultado de cada consulta em cada linha.