Comparar dos columnas con valores parciales y obtener información sobre la coincidencia y el número de columna

Question

Utilicé BUSCARV, COINCIDIR, ÍNDICE e incluso el complemento Fuzzy. Estoy seguro de que uno de estos o una combinación podría funcionar, simplemente no tengo los conocimientos necesarios para hacerlo funcionar y aparece "#N/A" todo el tiempo...

Problema: Tengo 2 columnas, con diferente número total de artículos, llenas de referencias. Uno tiene parcialmente los mismos números que el otro, con algunos cambios, como este:

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

Pero tengo miles de números y quiero hacer coincidir cada celda de A con todo el rango de B y si hay una coincidencia, incluso en la celda B6544, quiero saberlo y obtener algo (en la columna C) como "Verdadero B6544".

Un (pequeño) ejemplo de lo que estoy tratando (y no las referencias exactas, son datos internos sensibles):

       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 puede ver, J1 coincide parcialmente con B1, por lo que H1 devuelve "Verdadero, B1".

Como dije anteriormente, probé COINCIDIR, BUSCARV, ÍNDICE y el complemento Fuzzy. Entiendo que tengo que hacer algo como:

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

Pero nada parece funcionar... ¡se agradecerá cualquier ayuda!

Answer 1

Pruebe esta pequeña función definida por el usuario:

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 muestra la ilustración, enK1ingresar:

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

y copie hacia abajo.

La rutina elimina el dígito inicial de la subcadena e intenta encontrarlo dentro de la columna.

Answer 2

Como Excel no admite expresiones regulares, no creo que exista una fórmula que no necesite una columna auxiliar. En mi ejemplo,

• A contiene los números con un "-"
• B contiene los números que coinciden
• C contiene los valores de B sin el primer dígito.
• D es "FALSO" si el valor de A no coincide con ninguno en la columna B, o "VERDADERO", + referencia a la celda coincidente.

La fórmula en C1 es:

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

Esto elimina el primer dígito de B1.

La fórmula en D1 es:

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

Tenga en cuenta que, aunque esto debería producir el resultado deseado, recomendaría dividir VERDADERO/FALSO y la referencia de celda en dos columnas. Por lo tanto, solo explicaré la ADDRESSparte que le dará la referencia de la celda si se encuentra una coincidencia o un error en caso contrario.

VLOOKUPno es útil aquí, porque devolverá un valor en la misma fila que el valor coincidente. MATCH, por otro lado, devuelve la fila del valor coincidente.

• MID(A1,2,SEARCH("-",A1)-2)devuelve la subcadena de A1, comenzando con el segundo carácter, hasta la primera aparición de "-" y excluyéndola. Este es el valor que buscamos ( lookup_value).
• MATCH(lookup_value, C$1:C$10, 0)devolverá la fila de la primera aparición de lookup_value. Como no podemos aplicar una función al rango y Excel no admite expresiones regulares, necesitamos la columna auxiliar C. El último parámetro ( 0) es necesario porque los valores no están ordenados. Tenga en cuenta que el valor de fila devuelto es relativo al rango especificado, por lo que si su rango no comienza en la fila 1, tendrá que tenerlo en cuenta (por ejemplo, sumándolo ROW([first cell])-1al resultado de MATCH).
• ADDRESS(matched_row, COLUMN(B1))produce la referencia de celda. Puede usar el número absoluto de la fila en lugar de COLUMN(B1)si lo desea, pero eso no sería tan legible para los humanos.

Tienes que decidir por ti mismo qué referencias deben ser absolutas o relativas.

Answer 3

Estoy intentando responder parcialmente a su pregunta ya que aún no tenemos la imagen completa.

Agregué dos columnas auxiliares para procesar los datos y compararlos, lo que le brindará una mejor comprensión.

La C-helpercolumna elimina el 0principio y el final - number.
La I- helpercolumna elimina el dígito inicial (o el primer dígito) del J col.
Luego hice una comparación vlookupde datos y descubrí si los datos se encuentran o no según el resultado de vlookup(). I-helperC - helper

La fórmula que tengo en

• 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")

La siguiente parte del problema es localizar dónde se encontró esta entrada. Si se encuentra la entrada, se devuelve su dirección o la cadena Not_Available is returned. Los datos de muestra ahora se ven así

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

Editar

La fórmula que tengo en

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