Excel 2013, combine CountIF + VLookup

Question

Eu tenho o seguinte problema e preciso de conselhos sobre quais fórmulas usar ou se é melhor experimentar alguns códigos VBA? (Eu não faço ideia!): Estou trabalhando em uma planilha do Excel 2013 que possui os seguintes dados (Simplyfying para fazer você entender melhor)

NamesColumn | DatesColumn | OtherColumns

• Todas as entradas são ordenadas por data, do mais antigo ao mais recente
• Ambas as colunas conterão dados duplicados (uma combinação do mesmo nome e / ou data, eles diferem uns dos outros pelo que é colocado em OtherColumns)
• Várias vezes por semana, adiciono entradas à folha manualmente

Agora, eu preciso que o seguinte aconteça quando eu adicionar uma entrada: Eu quero ser alertado (de alguma forma, não importa como - formatação condicional, som ou até mesmo algo "escrito" em uma célula adjacente) se esse mesmo name (NamesColumn) apareceu 3 vezes ou mais nos últimos 720 dias (DatesColumn).

Também estou adicionando um pequeno exemplo com 5 entradas e como a planilha deve se comportar toda vez que eu adicionar uma entrada (de 1 a 5)

1. Bob | 01.01.2010 - > nada acontece apenas 1 bob nos 720 dias anteriores a 01.01.2010
2. Bob | 01.01.2012 - > nada acontece apenas 1 bob nos 720 dias anteriores a 01.01.2012
3. Bob | 01.02.2012 - > nada acontece apenas 2 bob nos 720 dias anteriores a 01.02.2012
4. Bob | 01.03.2012 - > ALERTA! Bob aparece 3 ou mais vezes nos 720 dias anteriores a 01.03.2012
5. Bob | 01.01.2016 - > nada acontece apenas 1 bob nos 720 dias anteriores a 01.01.2016

Answer 1

Supondo que a primeira linha tem rótulos e os dados começam na linha 2:

Selecione a célula A2 até a última célula de dados na coluna A e crie um novo formato condicional com uma regra. Use esta regra:

=COUNTIFS($B:$B,"<="&$B2,$B:$B,">="&$B2-720,$A:$A,$A2)>=3

Se você transformar a tabela de entrada de dados em uma tabela do Excel (usando Inserir tabela), o formato condicional será aplicado automaticamente às linhas adicionadas à tabela.

    

Answer 2

Você pode usar uma fórmula para mostrar o número de ocorrências nos últimos 720 dias:

=COUNTIFS($A$2:$A8,A8,($B$2:$B8),">="&B8-$C$1)

Você pode aplicar a formatação condicional para destacar as ocorrências frequentes.