OK, aqui está uma solução que funciona, mas pode causar danos cerebrais ao configurá-lo. Eu construí um passo de cada vez, calculando um conjunto de coisas que foram usadas pelos próximos cálculos. Uma vez que eu tinha um modelo de trabalho, trabalhei de trás para frente, substituindo as fórmulas reais pelas referências de célula, de modo que todas as fórmulas se referiam apenas às suas listas reais e não aos cálculos intermediários. As fórmulas cresceram rapidamente. De fato, a primeira tentativa produziu fórmulas que excederam a capacidade da célula. Eu divido em duas mesas, a primeira alimentando a segunda. As tabelas são muito grandes e você teria ido louco demais para obter todas as referências de célula apontando para os lugares certos para preencher as fórmulas em duas direções ao longo das tabelas. Então, adicionei algumas referências indiretas para que as fórmulas possam ser simplesmente copiadas e coladas e funcionem sem a limpeza manual. Infelizmente, isso produziu algumas fórmulas bem grandes.
Vou explicar isso como um exemplo localizado em lugares específicos em uma planilha. Se você precisar localizar as partes em outro lugar, edite todas as referências de linha e coluna na primeira célula e copie e cole para preencher as tabelas. Para sua própria sanidade, configure alguns exemplos conhecidos para que você possa verificar se as primeiras linhas e colunas em cada tabela estão funcionando antes de preencher a coisa toda. Tome um par de aspirina profilática e nós começaremos.
Isso é baseado na sua Lista A nas colunas A a F com dados que começam na linha 1 (2.000 linhas). A lista B está nas colunas H a J com os dados que começam na linha 1 (30 linhas).
A primeira tabela começa em L1. Esta tabela cria uma lista das posições das entradas da Lista B nos registros da Lista A. Por exemplo:
Position: 1 2 3 4 5 6
So if a List A record contains: 1 3 3 5 7 9
and a List B record contains: 1 3 7
the entry in this table will be: 1 2 5 (stored as a single number: 125)
Se o registro da Lista B não corresponder ao registro da Lista A, haverá um # N / D na célula. O layout desta tabela é assim:
[L] [M] [N] [O]
[1] <=======List B Row========>
[2] List A Row 1 2 3 ...
[3] 1
[4] 2
[5] 3
...
Você precisa realmente colocar os números de linha como cabeçalhos de coluna na linha 2 das colunas de M a AP e como rótulos de linha na coluna L. Essas são as fórmulas usadas como ponteiros. Existem 30 colunas de dados, uma para cada linha de entradas da Lista B, e você terá 2.000 linhas, representando as entradas na Lista A, iniciando na linha 3. Cada célula da tabela reflete uma entrada da Lista B versus uma entrada da Lista A . Esta é a fórmula para o M3:
=MATCH(INDIRECT("H"&M$2),$A1:$F1,0)&MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
+MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)&MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
+MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)
+MATCH(INDIRECT("J"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
+MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)+1, , )&":$F"&$L3),0)
Eu quebrei a fórmula aqui para torná-la mais legível, mas é tudo uma fórmula. Verifique se você está trabalhando em M3 a N4 com alguns dados de amostra e copie e cole para preencher a tabela.
A segunda tabela começa em AR1. Esta tabela está estruturada da mesma maneira:
[AR] [AS] [AT] [AU]
[1] <=======List B Row========>
[2] List A Row 1 2 3 ...
[3] 1
[4] 2
[5] 3
...
Esta tabela funciona de maneira semelhante à primeira - cada célula representa os resultados de um registro da Lista B versus um registro da Lista A. Esta tabela contém seu restante. Então, no exemplo que dei para a primeira tabela, o restante seria 359:
So if a List A record contains: 1 3 3 5 7 9
and a List B record contains: 1 3 7
the remainder is: 3 5 9
A fórmula que entra na célula AS3 é:
=IF(ISNA(M3),"",IF(ISERROR(FIND(COLUMN(INDIRECT("a"&$AR3)),M3)),INDIRECT("a"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("b"&$AR3)),M3)),INDIRECT("b"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("c"&$AR3)),M3)),INDIRECT("c"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("d"&$AR3)),M3)),INDIRECT("d"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("e"&$AR3)),M3)),INDIRECT("e"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("f"&$AR3)),M3)),INDIRECT("f"&$AR3),""))
Cada célula nesta tabela conterá o restante ou um caractere nulo se não houver correspondência.
Você queria obter um resumo dos resultados de cada registro da Lista A. Como cada linha da tabela representa um registro da Lista A, o resumo pode ir no final de cada linha da tabela. As 30 colunas da tabela terminam na coluna BV, portanto, os resultados estão na coluna BW. A fórmula para o BW3 será:
=AS3&IF(ISBLANK(AS3),""," ")&AT3&IF(ISBLANK(AT3),""," ")& ... &BV3&IF(ISBLANK(BV3),""," ")
Em vez de mostrar todos os 30 termos aqui, isso mostra apenas os dois primeiros e os últimos. Siga o mesmo padrão para adicionar o resto. Ele constrói a sequência de resultados concatenando os resultados de cada correspondência. Se houver um valor em uma célula, ele adicionará um espaço antes do próximo valor. Se você quiser um delimitador diferente, altere o espaço para outra coisa, como espaço de vírgula. Copie esta fórmula na coluna BW para todas as linhas.
Este provavelmente não é o lugar mais útil para os resultados. Depois de ter tudo funcionando, você pode mudar as coisas. Na verdade, se você mover qualquer coisa, você pode ter uma grande limpeza de referências de células. Seria mais sensato criar apenas a saída desejada em outro local e usar referências de célula para se referir ao que já está configurado.