No Excel, existe uma fórmula do Excel que posso usar para preencher a coluna B (no meu exemplo abaixo) que retornará o número da última linha que contém uma string específica, antes da linha atual?
Os dados não são contíguos.
Por exemplo, os dados podem ser:
| A (value) B (prev recent instance)
----------------------------------------------
1 | Mike
2 | John
3 | Mike 1
4 | Tony
5 | Mike 3
6 | John 2
Em seguida, desejarei expandi-lo para que a Coluna C mostre a instância mais antiga.
Parece muito semelhante a esta pergunta: O que é uma fórmula do Excel que retornará o número da última linha de uma célula dentro de um intervalo que contém dados específicos?, no entanto, essa pergunta e resposta exigem que os dados sejam contíguos.
Editar: Gostaria que a resposta fosse uma fórmula de planilha, porém se você achar mais apropriado implementar via VBA, inclua isso também em sua resposta com prós e contras de cada abordagem.
Responder1
Para encontrar a instância mais antiga, mencionada abaixo é a fórmula: -
=LOOKUP(2,1/(A:A=A2),ROW(A:A))
Para sua melhor compreensão, abaixo está a captura de tela
Responder2
Supondo que seus dados comecem em A1, tente esta "fórmula de matriz" em B2
=IF(COUNTIF(A$1:A1,A2),MATCH(2,1/(A$1:A1=A2)),"")
confirmado com CTRL+SHIFT+ENTER e copiado
ou você pode usar esta versão sem array
=IF(COUNTIF(A$1:A1,A2),MATCH(2,INDEX(1/(A$1:A1=A2),0)),"")
No Excel 2007 ou posterior você pode simplificar com IFERROR, ou seja, a versão do array torna-se
=IFERROR(MATCH(2,1/(A$1:A1=A2)),"")
Para a linha "1ª instância" na coluna C, tente esta fórmula em C1 copiada
=MATCH(A1,A$1:A1,0)
Nota: as fórmulas não retornam estritamente "números de linha", mas os números relativos das linhas dos seus dados, ou seja, a linha 1 dos seus dados retorna 1 etc.