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.