excel - true - lookup if

Excel combina Vlookups

Question

Tengo dos archivos, uno es un registro de proyecto que contiene información clave de un proyecto y el otro es un registro de riesgos.

Hay una relación 1: m entre las entradas en el registro y el registro de riesgos. Lo que tengo que hacer es combinar todos los riesgos de un proyecto en una celda dentro del archivo de registro del proyecto.

El campo coincidente en ambos archivos es el campo ID del proyecto

¿Hay alguna manera de hacerlo utilizando una variante de vlookup o vlookups anidados múltiples?

Answer 1

REEDITAR:

Es posible que necesite escribir una función definida por el usuario o escribir una macro (código en el mismo enlace)

Answer 2

Este es el enfoque de función definido por el usuario que mencioné (adaptado de una variante VLOOKUP diferente que ya había hecho):

'' Acts like VLOOKUP in a 1-to-many scenario by concatenating all values in matching rows
'' instead of just returning the first match
Public Function VLOOKUP_MANY(lookup_value As String, lookup_range As Range, column_number As Integer, Optional delimiter As Variant) As Variant
    Dim vArr As Variant
    Dim i As Long
    Dim found As Boolean: found = False

    '' Set default delimiter
    If IsMissing(delimiter) Then delimiter = ", "

    '' Get values
    vArr = lookup_range.Value2

    '' If column_number is outside of the specified range, return #REF
    If column_number < LBound(vArr, 2) Or column_number > UBound(vArr, 2) Then
        VLOOKUP_MANY = CVErr(xlErrRef)
        Exit Function
    End If

    '' Search for matches and build a concatenated list
    VLOOKUP_MANY = ""
    For i = 1 To UBound(vArr, 1)
        If UCase(vArr(i, 1)) = UCase(lookup_value) Then
            VLOOKUP_MANY = VLOOKUP_MANY & delimiter & vArr(i, column_number)
            found = True '' Mark at least 1 result
        End If
    Next

    If found Then
        VLOOKUP_MANY = Right(VLOOKUP_MANY, Len(VLOOKUP_MANY) - Len(delimiter)) '' Remove first delimiter
    Else
        VLOOKUP_MANY = CVErr(xlErrNA) '' If no matches found, return #N/A
    End If
End Function

Esto buscará la primera columna en el rango especificado para el valor especificado (igual que VLOOKUP), pero devuelve los valores en el número de columna especificado concatenado. Devolverá # N / A cuando no se encuentren coincidencias, y #REF si se especifica un valor no válido para el número de columna (por ejemplo, elige la columna 5 pero solo tenía una tabla de 4 columnas).

En caso de que no conozca las funciones definidas por el usuario, puede simplemente copiar este código VBA en el VBE para un módulo en su libro de trabajo. Presiona Alt + F11, ve a Insert > Module en la parte superior de la pantalla, luego pega este código en el archivo en blanco que se abre. Cuando vaya a guardar, tendrá que guardar su libro de trabajo como Macro-Habilitado (.xlsm) para mantener el código en funcionamiento. Excel le recordará esto en la pantalla de guardar.

Esté advertido: va a ser más lento que VLOOKUP como resultado de tener que buscar en todo el rango de búsqueda en lugar de poder detenerse en la primera coincidencia que encuentre.

Si está dispuesto a utilizar una fórmula de matriz en su lugar, hay formas de acelerar este tipo de funcionalidad para conjuntos de datos muy grandes ...

Versión diferente que aprovecha algunos de los beneficios de las fórmulas de matriz para almacenar valores de búsqueda y acelerar las llamadas siguientes:

'' Acts like VLOOKUP in a 1-to-many scenario by concatenating all values in matching rows
''  instead of just returning the first match
'' Utilizes a dictionary to speedup multiple matches (great for array formulas)
Public Function VLOOKUP_MANY_ARRAY(lookup_values As Range, lookup_range As Range, column_number As Integer, Optional delimiter As Variant) As Variant
    Dim vHaystack As Variant, vNeedles As Variant
    Dim i As Long
    Dim found As Boolean: found = False
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

    '' Set default delimiter
    If IsMissing(delimiter) Then delimiter = ", "

    '' Get values
    vHaystack = lookup_range
    vNeedles = lookup_values

    '' If column_number is outside of the specified range, return #REF
    If column_number < LBound(vHaystack, 2) Or column_number > UBound(vHaystack, 2) Then
        VLOOKUP_MANY_ARRAY = CVErr(xlErrRef)
        Exit Function
    End If

    '' Add values to a lookup dictionary
    For i = 1 To UBound(vHaystack, 1)
        If dict.Exists(UCase(vHaystack(i, 1))) Then
            dict.Item(UCase(vHaystack(i, 1))) = dict.Item(UCase(vHaystack(i, 1))) & delimiter & vHaystack(i, column_number)
        Else
            dict.Add UCase(vHaystack(i, 1)), vHaystack(i, column_number)
        End If
    Next

    Dim outArr As Variant
    If IsArray(vNeedles) Then '' Check number of lookup cells
        '' Build output array
        ReDim outArr(1 To UBound(vNeedles, 1), 1 To 1) As Variant

        For i = 1 To UBound(vNeedles, 1)
            If dict.Exists(UCase(vNeedles(i, 1))) Then
                outArr(i, 1) = dict.Item(UCase(vNeedles(i, 1)))
            Else
                outArr(i, 1) = CVErr(xlErrNA)
            End If
        Next
    Else
        '' Single output value
        If dict.Exists(UCase(vNeedles)) Then
            outArr = dict.Item(UCase(vNeedles))
        Else
            outArr = CVErr(xlErrNA)
        End If
    End If

    VLOOKUP_MANY_ARRAY = outArr
End Function

Esto crea un Dictionary , que es una estructura especial que es realmente buena para buscar valores. Hay un poco de sobrecarga adicional involucrado en la construcción, pero una vez que tenga la estructura, puede hacer búsquedas en ella muy rápidamente. Esto es especialmente bueno con fórmulas de matriz, que es básicamente cuando la misma fórmula se coloca en una colección completa de células, luego la función se ejecuta una vez y devuelve valores para cada celda (en lugar de ejecutar una sola vez por separado para un grupo de celdas ) Introdúzcalo como una fórmula de matriz con CTRL + MAYÚS + INTRO, y haga que el primer argumento se refiera a todos sus valores de búsqueda en lugar de a uno solo.

Funcionará sin ser utilizado como una fórmula de matriz, pero será algo más lenta que la primera función en esa situación. Sin embargo, si lo usa en una fórmula de matriz, verá grandes aceleraciones.