Localizando datas correspondentes para o mesmo usuário em duas tabelas no Excel

Question

Eu tenho duas listas, cada uma contendo uma lista de intervalos de tempo para vários usuários. Quero identificar lugares onde uma data de início para um usuário específico em uma lista é a mesma (ou dentro de 1 dia, se possível, mas vou me contentar com uma correspondência exata) como a data de término do usuário na outra lista ou vice versa.

Lista 1

User        Start       Stop
User1       1/1/2014    3/15/2014
User1       6/1/2014    12/1/2015
User2       3/1/1998    9/2/2016
User3       4/10/2007   3/5/2011
User3       9/6/2013    9/8/2013
User3       5/20/2014   5/25/2014

Lista 2

 User      Start        Stop
 User1     3/15/2014    5/31/2014
 User1     12/5/2014    2/10/2016
 User1     8/2/2016     1/1/2017
 User2     5/15/2010    6/25/2010
 User2     2/1/2012     3/1/2012
 User3     6/1/2012     9/5/2013
 User3     4/25/2014    2/8/2017

No exemplo acima:

• a data de parada na primeira entrada do Usuário1 é igual à data de Início na primeira entrada do Usuário1 na Lista 2. Não há outras datas correspondentes, portanto, desejo destacar apenas essa ocorrência.

• Para o User2, a Lista 1 mostra um grande intervalo de tempo. Embora a Lista 2 tenha dois intervalos de tempo que estão completamente dentro do período de tempo da Lista 1, as datas de início / término não são as mesmas, portanto, não são significativas.

• Para o Usuário 3, a data de Início na primeira entrada na Lista 1 é o dia após a data de Parada na primeira entrada do Usuário 3 na Lista 2. Por isso, gostaria de destacar isso de alguma forma. Os outros quadros de tempo não estão associados de forma alguma, por isso não são significativos.

Alguém pode me ajudar a encontrar minhas correspondências? Eu tenho milhares de linhas. Obrigado!

EDITAR:

Aqui estão as imagens que mostram onde estou -

    

Answer 1

Para verificar apenas correspondências exatas, a maneira mais fácil é usar COUNTIFS :% =OR(COUNTIFS(List2[User],[@User],List2[Start],[@Stop]),COUNTIFS(List2[User],[@User],List2[Stop],[@Start]))

Procurando por diferenças de 1 dia também, você precisa usar SUMPRODUCT :% =OR(SUMPRODUCT((List2[User]=[@User])*(ABS(List2[Start]-[@Stop])<=1)),SUMPRODUCT((List2[User]=[@User])*(ABS(List2[Stop]-[@Start])<=1)))

No exemplo abaixo, adicionei o cálculo em uma nova coluna à lista1, você pode adicioná-lo a ambas as listas e filtrar por correspondências.

Vocêpodeverocálculopassoapassoindoparaaguia"Fórmulas" - "Avaliar fórmula"