Tengo 600 códigos de artículo y cada uno está codificado con atributos en el código de artículo general. Por ejemplo, podríamos tener el artículo simple 600, luego 600BK (cuerpo negro) y 600BKR (cuerpo negro, detalle de color rojo) y 600BKR-YEL (cuerpo negro, detalle de color rojo, base amarilla).
Entonces tengo la lista de códigos de artículos:
600
600BK
600BKR
600BKR-YEL
y luego, en una hoja de trabajo separada, una lista de búsqueda de cada código y lo que representa:
BK Black Body
R Red Detail
YEL Yellow Base
Me gustaría escribir una función que encuentre todos los códigos en el título del elemento que se encuentra en la hoja de búsqueda y luego devuelva todos los valores correspondientes para cada código encontrado, preferiblemente en una celda.
600
600BK Black Body
600BKR Black Body Red Detail
600BKR-YEL Black Body Red Detail Yellow Base
es posible?
Respuesta1
Yo haría algo como lo siguiente. Suponiendo que su lista de códigos de artículos está en la columna A de la Hoja 1 y, digamos, las filas 2 a 20, y su lista de búsqueda está en las columnas A y B de la Hoja 2, filas 2 a 10:
=IFERROR(VLOOKUP(MID(A2,4,2),Sheet2!$A$2:$B$4,2,0),"")&" "
&IFERROR(VLOOKUP(MID(A2,6,1),Sheet2!$A$2:$B$4,2,0),"")&" "
&IFERROR(VLOOKUP(MID(A2,8,3),Sheet2!$A$2:$B$4,2,0),"")
en la celda B2 y luego se copia para todas las filas de la Hoja1.
Si bien coloca las búsquedas en una única fórmula, este enfoque sería muy engorroso si tiene muchos códigos. Le recomendaría que inserte filas auxiliares en la Hoja1 para cada posición del código (por ejemplo, mid(A2,4,2), y luego las concatene en una sola cadena.
Respuesta2
La respuesta corta es "Sí", pero una forma es bastante más larga.
Dado que hay muchas posibilidades que no están excluidas en su pregunta (como elementos simples codificados con más de tres números o alfanuméricos, más de tres códigos por elemento simple, códigos que varían en significado dependiendo del elemento simple, etc.), sugeriría que lo más seguro sea comenzar con el análisis. los códigos. Esto debería evitar complicaciones con GR como cuerpo gris con detalle rojo (¡a menudo un esquema de color compatible!) o cuerpo verde.
Suponiendo que se hayan analizado (en tres columnas B:D comenzando en la Fila 2), a partir de entonces es fácil, con su tabla de búsqueda (la que está en la hoja separada) diga con nombre codes:
=VLOOKUP(B2,codes,2,0)&VLOOKUP(C2,codes,2,0)&VLOOKUP(D2,codes,2,0)
en su hoja de códigos de artículos. Para obtener espacios entre los valores, he asumido que todas las entradas en la columna de la derecha de su matriz de códigos terminan en un espacio (bastante fácil de organizar, como con =A2&" " copiado, etc.).
Entonces, la parte difícil puede ser analizar los códigos de los códigos de artículos, para lo cual sugeriría agregar columnas como se muestra a continuación (más si hay más de tres códigos):
con fórmulas como las siguientes:
La columna B sirve para definir dónde empezar a buscar códigos (en caso de que los códigos simples no sean tres numéricos). Las columnas C:D indican dónde comenzar a buscar el siguiente código/la longitud del siguiente código. Estoy de acuerdo en que no es elegante, pero sí relativamente versátil. Asegúrese de que el análisis sea correcto a la izquierda antes de trabajar hacia la derecha.
Después de un análisis exitoso, sugiero copiar/pegar valores/especiales (para deshacerse de las fórmulas) y luego reemplazarblancocon un punto (para evitar que la fórmula de búsqueda suene errónea sin hacerla más compleja)*. Además, suponiendo que su lista de códigos de artículos está en la Columna A, elimine las Columnas B: F antes de aplicar la fórmula de búsqueda como se indicó anteriormente (o ajuste las referencias para adaptarlas) y agregue más búsquedas si es necesario.
*Supervisión: asegúrese de agregar un punto en una celda en cada columna de la tabla de búsqueda.
Agregue columnas para c5, c6 y c7 para permitir (a) una longitud máxima de 7 con (b) el peor de los casos (es decir, todos de un solo carácter).
Respuesta3
Aquí hay una versión que utiliza la función de búsqueda (versión de búsqueda que no distingue entre mayúsculas y minúsculas). La configuración es la siguiente.
En la hoja 1, los códigos a buscar comienzan en la columna A de la Hoja 1. El resultado final estará en la columna B. Las columnas C, D, etc. tienen los códigos únicos dispuestos horizontalmente en la fila 1, es decir, "BK" en C1, "R" en D2, etc. La forma práctica de lograr esto es simplemente copiar la lista de códigos en la tabla de búsqueda y Pegado Especial Transponerlos horizontalmente en las celdas C1, D1, etc.
Luego primero en la celda B2 ingresa la siguiente fórmula:
=IF(NOT(ISERROR(SEARCH(C$1,$A2))),VLOOKUP(C$1,Sheet2!$A$2:$B$4,2,0),"")
Copie esta fórmula en las filas desde la columna C hasta cuántas columnas de código creó en la fila 2.
Finalmente, en la celda C2, concatena todos los resultados de la fila 2, es decir, la fórmula
=D2&" "&E2&" "&F2
etc., para todas las columnas con códigos en la fila 1. Este paso es tedioso, pero se puede acortar con la siguiente función de VBA, que permite concatenar todas las celdas de un rango:
Function Concat(useThis As Range, Optional delim As String) As String
' this function will concatenate a range of cells and return the result as a single string
' useful when you have a large range of cells that you need to concatenate
' source: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
Dim retVal As String, dlm As String, cell As Range
retVal = ""
If delim = Null Then
dlm = ""
Else
dlm = delim
End If
For Each cell In useThis
If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
retVal = retVal & CStr(cell.Value) & dlm
End If
Next
If dlm <> "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
Concat = retVal
End Function
Insertaría y copiaría esta función en un módulo en Developer VBA. El uso es simple: concat(C1:D1," "), por ejemplo.
Tenga en cuenta que este enfoque funciona para todos los códigos de 2 caracteres y para todos los códigos de 1 carácter si no están en los códigos de 2+ caracteres, es decir, si no hay pares de códigos como "R" y "BR".