Comparando duas colunas com valores parciais e obtendo informações sobre correspondência e número da coluna

Question

Usei VLOOKUP, MATCH, INDEX e até o complemento Fuzzy. Tenho certeza de que um desses ou uma combinação pode funcionar, só não tenho o conhecimento para fazer funcionar e obter "#N/A" o tempo todo...

Problema: Tenho 2 colunas, com números totais de itens diferentes, cheias de referências. Um tem parcialmente os mesmos números do outro, com algumas alterações, como esta:

Column A typical number (ex): 025983553-1 
Column B typical number (ex): 225983553

Mas eu tenho milhares de números e quero combinar cada célula de A com todo o intervalo de B e se houver correspondência, mesmo na célula B6544, quero saber e obter algo (na coluna C) como "Verdadeiro B6544".

Um (pequeno) exemplo do que estou lidando (e não as referências exatas, esses são dados sensatos internos):

       B                J       H
  1 025983553-1     225983553   True, B1
  2 025973223-1     222222345   False
  3 025965463-2     233444667   False
  4 025911122-4     211198989   False
  5 025998764-1     212989238   False
  6 025925925-3     224397501   False
  7 025900000-2     225973223   True, B2
  8 025999999-5     223334445   False
  9 025965453-6     211100110   False
 10 025943536-2     225911122   True, B4
 ...

Como você pode ver, J1 é uma correspondência parcial com B1, então H1 retorna “Verdadeiro, B1”.

Como dito acima, experimentei MATCH, VLOOKUP, INDEX e o complemento Fuzzy. Eu entendo que preciso fazer algo como:

 =VLOOKUP(B1, $J$1:$J$10, valueThat IwantIThink, FALSE)

Mas nada parece funcionar... qualquer ajuda será apreciada!

Answer 1

Experimente esta pequena função definida pelo usuário:

Public Function PartialMatch(r1 As Range, r2 As Range) As Variant
    Dim boo As Boolean, v As Variant, r As Range
    boo = False
    v = Mid(r2.Text, 2)
    For Each r In r1
        If InStr(1, r.Text, v) > 0 Then
            PartialMatch = "True, " & r.Address(0, 0)
            Exit Function
        End If
    Next r
    PartialMatch = boo
End Function

Como mostra a ilustração, emK1digitar:

=partialmatch($B$1:$B$10,J1)

e copie para baixo.

A rotina retira o dígito inicial da subsequência e tenta localizá-lo na coluna.

Answer 2

Como o Excel não suporta expressões regulares, não creio que exista uma fórmula que não precise de uma coluna auxiliar. No meu exemplo,

• A contém os números com um "-"
• B contém os números que correspondem
• C contém os valores de B sem o primeiro dígito
• D é "FALSO" se o valor de A não corresponder a nenhum na coluna B, ou "VERDADEIRO", + referência à célula correspondente.

A fórmula em C1 é:

=RIGHT(B1,LEN(B1)-1)

Isso remove o primeiro dígito de B1.

A fórmula em D1 é:

=IFERROR("TRUE, "&ADDRESS(MATCH(MID(A1,2,SEARCH("-",A1)-2),C$1:C$10,0),COLUMN(B1)),FALSE())

Observe que, embora isso deva produzir a saída desejada, eu recomendaria dividir TRUE/FALSE e a referência de célula em duas colunas. Portanto, explicarei apenas a ADDRESSparte que lhe dará a referência da célula se for encontrada uma correspondência ou, caso contrário, um erro.

VLOOKUPnão é útil aqui, porque retornará um valor na mesma linha que o valor correspondente. MATCH, por outro lado, retorna a linha do valor correspondente.

• MID(A1,2,SEARCH("-",A1)-2)retorna a substring de A1, começando com o segundo caractere, até e excluindo a primeira ocorrência de "-". Este é o valor que procuramos ( lookup_value).
• MATCH(lookup_value, C$1:C$10, 0)retornará a linha da primeira ocorrência de lookup_value. Como não podemos aplicar uma função ao intervalo e o Excel não suporta expressões regulares, precisamos da coluna auxiliar C. O último parâmetro ( 0) é necessário porque os valores não estão ordenados. Observe que o valor da linha retornado é relativo ao intervalo especificado, portanto, se o seu intervalo não começar na linha 1, você terá que levar em conta isso (por exemplo, adicionando ROW([first cell])-1ao resultado de MATCH).
• ADDRESS(matched_row, COLUMN(B1))produz a referência da célula. Você pode usar o número absoluto da linha em vez de, COLUMN(B1)se quiser, mas isso não seria tão legível por humanos.

Você tem que decidir por si mesmo quais referências devem ser absolutas ou relativas.

Answer 3

Estou tentando responder parcialmente à sua pergunta, pois ainda não temos o quadro completo.

Adicionei duas colunas auxiliares para processar os dados e compará-los - o que lhe dará uma melhor compreensão.

A C-helpercoluna retira 0o início e o final - number.
A I- helpercoluna remove o dígito inicial (ou o primeiro dígito) do arquivo J col.
Então eu fiz uma análise vlookupde dados e descobri se os dados foram encontrados ou não com base no resultado de vlookup(). I-helperC - helper

A fórmula que tenho em

• C - helper:==MID(B4,2,LEN(B4)-3)
• I - helper:=MID(D4,2,LEN(D4)-1)
• vlookup col: =IF(ISNA(VLOOKUP(E4,$C$4:$C$13,1,FALSE)),"not_found","found")

A próxima parte do problema é localizar onde essa entrada foi encontrada. Se a entrada for encontrada - seu endereço será retornado, caso contrário, a string Not_Available is returned. Os dados de amostra agora se parecem com isto

ABC - ajudante JI - ajudante vlookup col HI X-helper
1 025983553-1 25983553 225983553 25983553 encontrado Verdadeiro, B1 $C$4
2 025973223-1 25973223 222222345 22222345 not_found FALSE Not_Available
3 025965463-2 25965463 233444667 33444667 not_found FALSE Not_Available
4 025911122-4 25911122 211198989 11198989 not_found FALSE Not_Available
5 025998764-1 25998764 212989238 12989238 not_found FALSE Not_Available
6 025925925-3 25925925 224397501 24397501 not_found FALSE Not_Available
7 025900000-2 25900000 225973223 25973223 encontrado Verdadeiro, B2 $C$5
8 025999999-5 25999999 223334445 23334445 not_found FALSE Not_Available
9 025965453-6 25965453 211100110 11100110 not_found FALSE Not_Available
10 025943536-2 25943536 225911122 25911122 encontrado Verdadeiro, B4 $C$7

Editar

A fórmula que tenho em

• X - helper:=IF(F4="found",CELL("address",INDEX($B$4:$C$13,MATCH(E4,$C$4:$C$13,0),2)),"Not_Available")