Como extrair o restante de uma correspondência de 5 dígitos em 9

Question

Eu estou tentando extrair o "restante" (a parte não correspondente) de uma pesquisa usando o Excel 2003. Eu tentei usar várias combinações de funções, mas parece que não há nenhuma função específica que pode retornar a parte não correspondida de um registro encontrado através de uma partida com outra parte do registro.

Aqui está uma ilustração do que estou tentando fazer. Eu tenho duas listas. Lista A tem 2000 registros. Cada registro é uma matriz de 9 células, cada uma contendo um único dígito nas colunas A a I. Os dígitos estão sempre em ordem crescente. Então, esses dados são assim:

    [A]  [B]  [C]  [D]  [E]  [F]  [G]  [H]  [I]
     1    2    2    3    4    5    7    7    9
     2    4    5    5    6    6    8    9    9
    . . . etc.

Lista B tem 30 registros. Estes são os valores pesquisados nos registros da Lista A. Cada registro é uma matriz de 5 células, cada uma contendo um único dígito nas colunas L a P. Esses dígitos estão sempre em ordem crescente. Então, esses dados são assim:

    [L]  [M]  [N]  [O]  [P]
     1    1    3    4    5
     2    3    5    7    9
     1    2    3    4    5
    . . . etc.

Cada registro na Lista B é pesquisado na Lista A. É uma correspondência se todos os cinco dígitos no registro da Lista B corresponderem a cinco dos dígitos no registro da Lista A. Portanto, neste exemplo, o primeiro registro da Lista B não corresponde a nada na Lista A. O segundo e o terceiro registros da Lista B correspondem ao primeiro registro da Lista A e nenhum registro da Lista B corresponde ao segundo registro da Lista A.

Quando há uma correspondência, o "restante" são os outros quatro dígitos do registro da Lista A que não faziam parte da correspondência. Para as duas correspondências neste exemplo, isso seria ilustrado assim:

    List A record: 1    2    2    3    4    5    7    7    9
    List B record:      2         3         5    7         9
    Remainder:     1         2         4              7


    List A record: 1    2    2    3    4    5    7    7    9
    List B record: 1    2         3    4    5
    Remainder:               2                   7    7    9

Portanto, o resultado do primeiro registro da Lista A é: 1247 2779 e o resultado da segunda Lista Um registro é um espaço em branco.

Os valores nos registros da Lista B podem mudar, portanto, a solução precisa ser "genérica" para que os resultados possam ser atualizados sem alterar as fórmulas.

Eu tentei basear uma solução na função COUNT para contar a freqüência de cada dígito 0-9 em cada item da Lista A e usar as funções IF e AND (especificando quais e quantos de cada dígito é necessário para se qualificar como uma correspondência), para me informar quais itens da Lista B correspondem à Lista A. Minhas tentativas de usar outras funções não conseguiram extrair o restante da maneira que eu quero.

Esta questão é semelhante a Como extrair lembrete de uma correspondência no MS Excel 2003 , mas os padrões numéricos são diferentes. Uma solução foi encontrada para essa pergunta, mas é difícil descobrir como modificar a resposta para ajustar esse problema. De tempos em tempos, tenho problemas semelhantes dessa natureza, então espero uma resposta que inclua um processo que eu possa seguir para desenvolver soluções semelhantes para outros problemas semelhantes.

    

Answer 1

Comecei a criar uma resposta com base no método da pergunta anterior. Então percebi que você estava no caminho certo. Como tudo está em ordem crescente, você pode basear a resposta nas contagens de cada dígito. A função REPT irá construir uma string com base em quantas repetições de um caractere você precisa. A quantidade de cada dígito necessária para o restante é a quantidade no registro da Lista A menos a quantidade no registro da Lista B. Portanto, para uma única correspondência, digamos que os dígitos do registro A estão em A1: I1 e os dígitos do registro B estão em L1: P1. O restante seria:

    =REPT(1,COUNTIF(A1:I1,1)-COUNTIF(L1:P1,1))&
     REPT(2,COUNTIF(A1:I1,2)-COUNTIF(L1:P1,2))&
     REPT(3,COUNTIF(A1:I1,3)-COUNTIF(L1:P1,3))&
     REPT(4,COUNTIF(A1:I1,4)-COUNTIF(L1:P1,4))&
     REPT(5,COUNTIF(A1:I1,5)-COUNTIF(L1:P1,5))&
     REPT(6,COUNTIF(A1:I1,6)-COUNTIF(L1:P1,6))&
     REPT(7,COUNTIF(A1:I1,7)-COUNTIF(L1:P1,7))&
     REPT(8,COUNTIF(A1:I1,8)-COUNTIF(L1:P1,8))&
     REPT(9,COUNTIF(A1:I1,9)-COUNTIF(L1:P1,9))

Tudo isso é uma fórmula. Eu dividi em linhas para que as peças se alinhassem e você pudesse ver o padrão. Se não houver correspondência, isso produzirá um erro, que pode ser tratado posteriormente.

Você deseja produzir um relatório dos resultados de todas as correspondências. Então, podemos configurar uma estrutura para fazer isso.

Digamos que a Lista A esteja em A1: I2000 e a Lista B em L1: P30. Crie uma tabela no R1: AV2002. A tabela será um mecanismo para coletar os resultados da comparação de todos os registros B para todos os registros A. Ele também fornecerá um índice para representar os números de linha em cada lista, para que você possa usar uma fórmula geral e um endereçamento indireto para realizar as correspondências. Configure a tabela para ficar assim:

        [R]     [S]      [T]       [U]   ...  [AV]
[1]             <=======List B Row===============>
[2] List A Row   1        2         3    ...   30
[3]     1
[4]     2
[5]     3
    ...

Cada célula da tabela refletirá uma correspondência entre um registro B e um registro A. Na verdade, insira os números de linha da Lista A como rótulos na coluna R, começando na linha 3, e os números de linha da Lista B como títulos de coluna em S2: AV2. Esses números serão usados para apontar para os registros corretos.

Na fórmula fornecida anteriormente na resposta, os números de linha dos registros A e B são codificados. Para essa tabela, precisamos de uma fórmula geral que possa usar os rótulos de linha e coluna para se referir aos registros corretos a serem usados para cada célula. Por isso, substituímos os números das linhas por endereços indiretos com base nos rótulos. Cada referência a A1:I1 é substituída por:

    INDIRECT("$A"&$R3):INDIRECT("$I"&$R3)

e cada referência a L1:P1 é substituída por:

    INDIRECT("$L"&S$2):INDIRECT("$P"&S$2)

Observe os locais das $ âncoras nessas substituições. A célula S3 ficaria assim:

    =REPT(1,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),1)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),1))&
     REPT(2,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),2)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),2))&
     REPT(3,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),3)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),3))&
     REPT(4,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),4)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),4))&
     REPT(5,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),5)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),5))&
     REPT(6,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),6)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),6))&
     REPT(7,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),7)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),7))&
     REPT(8,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),8)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),8))&
     REPT(9,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),9)-COUNTIF(INDIRECT("$L"&S$2):INDIRECT("$P"&S$2),9))

Novamente, tudo isso é uma fórmula dividida em linhas para facilitar a leitura. Depois que o S3 estiver correto, copie essa fórmula para preencher todas as células da tabela. Você saberá se está correto com isto: as referências aos registros da Lista A serão idênticas para todas as células na mesma linha da tabela e o $R3 irá aumentar conforme você desce pela tabela (segunda linha da tabela, isso seja $R4 ). Referências aos registros da Lista B serão idênticas à medida que você desce em uma coluna. O S$2 mudará a letra da coluna conforme você passa por uma linha da tabela (segunda coluna da tabela, isso será T$2 ). Como verificação, T3 deve ficar assim:

    =REPT(1,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),1)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),1))&
     REPT(2,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),2)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),2))&
     REPT(3,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),3)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),3))&
     REPT(4,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),4)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),4))&
     REPT(5,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),5)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),5))&
     REPT(6,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),6)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),6))&
     REPT(7,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),7)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),7))&
     REPT(8,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),8)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),8))&
     REPT(9,COUNTIF(INDIRECT("$A"&$R3):INDIRECT("$I"&$R3),9)-COUNTIF(INDIRECT("$L"&T$2):INDIRECT("$P"&T$2),9))

Cada célula da tabela refletirá a correspondência entre um registro B e um registro A. Ele conterá o "resto" ou um erro sem correspondência. Você deseja obter um resumo dos resultados de cada registro da Lista A. Cada linha da tabela representa um registro da Lista A. Os resultados podem ser resumidos no final da tabela (coluna AW) ou à direita dos dados da Lista A (coluna J). A fórmula para o primeiro resumo será:

=IF(ISERROR(S3),"",S3&" ")&IF(ISERROR(T3),"",T3&" ")&IF(ISERROR(U3),"",U3&" ")& ... &IF(ISERROR(AV3),"",AV3)

Em vez de mostrar todos os 30 termos aqui, isso mostra apenas os três 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 essa fórmula na coluna de resumo de todas as linhas da Lista A.

    

Answer 2

Se você já tiver uma resposta para a correspondência, é provável que NOT(...) obtenha os resultados não correspondentes.