Preencher coluna de outra coluna para cada linha em um grupo

Question

Estou em um exercício de migração de dados e tenho uma planilha com mais de 100 mil linhas e, portanto, preciso de uma solução além de uma atualização manual.

Para o bem deste post vou simplificar o problema: tenho uma planilha com três colunas (A, B e C) com títulos [tipo de dados] da seguinte forma: Nome [texto], ID [número], CreateDate [data ]. Os dados foram classificados na coluna A para colocar deliberadamente em foco os valores duplicados nessa coluna. Os IDs são únicos; as datas sãonãoexclusivo.

No exemplo, há nove linhas que podem ser 'agrupadas' em três grupos em virtude de valores duplicados na coluna A. Portanto, para as linhas 2 e 3, o valor na coluna A é abc, para as linhas 4, 5 e 6, o valor na coluna A. é def, e para as linhas 7 a 10 é ghi.

A primeira tarefa é determinar o CreateDate mais recente em cada um dos 'grupos de linhas'. Portanto, neste exemplo, isso seria 11/05/1999 para as linhas 2 e 3, 12/03/2001 para as linhas 4 a 6 e 11/05/1999 para as linhas 7 a 10. Faço isso usando a fórmula de matriz {=MAX(IF(A2=$A:$A,$C:$C))} na coluna D.

A próxima tarefa está se mostrando difícil. Agora que determinei a última data para cada grupo de linhas, quero colocar o ID que corresponde a esse LatestDate na coluna E (em cada linha do grupo de linhas), para que o resultado fique parecido com a Coluna E no exemplo abaixo. Mas preciso fazer isso com uma fórmula/função, não manualmente. Isto é para um projeto de migração, então não precisa ser bonito.

Colocar o ID correspondente na coluna E na mesma linha é fácil (uma instrução IF) – veja as linhas 3, 6 e 9 – mas não estou fingindo que essa seja a solução. Mas não consigo descobrir como preencher as outras linhas em cada grupo com o mesmo ID - no meu exemplo anexo seriam as linhas 2, 4 e 5, 7 e 8 e 10. Não posso simplesmente fazer uma pesquisa na coluna C porque esses valores não são exclusivos. Depois de tentativas inúteis com várias fórmulas, fico perplexo. Meu palpite é que ele precisa contar com a coluna C (os valores correspondentes) e funções como INDEXe  MATCH... ou talvez VBA.

     +------+------+------------+------------+-----------------+
     |  A   |   B  |     C      |     D      |        E        |
+----+------+------+------------+------------+-----------------+
|  1 | Name |  ID  | CreateDate | LatestDate | CorrespondingID |
|  2 | abc  |   1  | 4/12/1998  | 5/11/1999  |         2       |
|  3 | abc  |   2  | 5/11/1999  | 5/11/1999  |         2       |
|  4 | def  |   3  | 1/12/1999  | 3/12/2001  |         5       |
|  5 | def  |   4  | 5/11/1999  | 3/12/2001  |         5       |
|  6 | def  |   5  | 3/12/2001  | 3/12/2001  |         5       |
|  7 | ghi  |  17  | 1/17/1999  | 5/11/1999  |        55       |
|  8 | ghi  |  42  | 2/4/1999   | 5/11/1999  |        55       |
|  9 | ghi  |  55  | 5/11/1999  | 5/11/1999  |        55       |
| 10 | ghi  |  83  | 3/28/1999  | 5/11/1999  |        55       |
+----+------+------+------------+------------+-----------------+

(Um exemplo mais curto está disponível como imagem.)

Answer 1

Minha referência éaqui. TLDR: use a versão sem array da fórmula de correspondência de índice.

Em E2 coloque:

=INDEX(B:B,MATCH(1,INDEX((A2=A:A)*(D2=C:C),0,1),0))

Idéia: o index() interno gera uma lista 0 e 1 que corresponde aos critérios de nome e data. então o index() externo é usado para 'carregar' o nome de A:A .

Answer 2

Esta solução usa o método de correspondência de deslocamento. Usando uma correspondência para encontrar a data e depois compensar com base na data para encontrar o ID correspondente:

=DESVIO(C2,COMBINAÇÃO(D2,C2:C,0)-1,-1)

Solte isso na célula E2 e arraste para baixo em E

Answer 3

Para uma determinada linha (por exemplo, Linha 2), você deseja encontrar o ID (Coluna  B) de uma Linha n(ou seja, Cell  ) onde o nome ( ) é igual ao nome da linha atual ( ) e o CriadoDate ( ) é igual ao LatestDate da linha atual ( ). Linha BnAnA2CnD2né único porque os IDs são únicos, e então “o”linha é a linha máxima. Uma resposta lógica seria uma pequena extensão da sua fórmula para Column  D:

=MAX(IF(AND(A2=$A:$A,D2=$C:$C), $B:$B))

Infelizmente, ANDparece não funcionar em fórmulas de matriz. Então usamos um truque padrão: TRUE = 1 (ou qualquer coisa diferente de zero) e  FALSE = 0, então podemos simular ANDcom multiplicação ( AND(TRUE,TRUE) =  TRUE assim como 1 × 1 = 1,  =  assim como 0 × AND(FALSE,anything)FALSEqualquer coisa = 0). Então mudamos o acima para

=MAX(IF((A2=$A:$A)*(D2=$C:$C), $B:$B))

(inserido como uma fórmula de matriz, com Ctrl+ Shift+ Enter, é claro):

Observe que isso funciona com CreatedDates não exclusivos. Funciona até mesmo se os CriadosDates não estiverem na mesma ordem dos IDs, como no exemplo acima (onde os dados são classificados por Colunas  Ae  B).