Concatenar contenido de celda condicional en filas

Question

Tengo una hoja de trabajo con miles de filas que contienen una identificación única Ay tres códigos de error diferentes M. Cada ID individual puede tener 1, 2 o 3 errores. Siempre estarán en el mismo orden.

He escrito a mano y Ncuál es el resultado deseado. He escrito una fórmula como esta en columnas O, P, Qcon el texto del error:

=IF(IFERROR(SEARCH("Brand is not valid", M42), "") <> "", "Brand", "")

Intenté algo como esto Rpero no funciona correctamente por razones obvias. ¿Hay alguna manera de hacer esto para que Rse vea así N?

=CONCATENATE(O42,"/", P42,"/",Q42)

Si no es posible hacerlo con la fórmula de Excel, ¿existe quizás una forma de VBA?

Answer 1

Vale, me costó un poco rascarme la cabeza, pero ya lo tengo:

Columnas O, P& Qtal como las tienes, tituladas Brand, Product& OEM. Convierta todo en una tabla con Ctrl- T(no es obligatorio, pero es útil, y mi columna Rse basa en ello, pero puede usar referencias de columnas si lo desea)

Columna R:

=IF(LEN([Brand])>0,[Brand],IF(LEN([Product])>0,[Product],IF(LEN([OEM])>0,[OEM],"")))

Columna S:

=IF(A2=A4,F2&"/"&F3&"/"&F4,IF(A2=A1,"",IF(A2=A3,F2&"/"&F3,F2)))

Desafortunadamente, parece que la única forma de usar referencias de tabla a una fila diferente es usar Offset, así que para hacerlo más simple, volví a las referencias de celda. Eso anula un poco el factor genial/práctico de convertir todo en una mesa en primer lugar, pero, como sea...

Y... Aquí hay una foto de cómo se ve:

Answer 2

Comenzaría optimizando sus fórmulas O, Py Q. Tu Actualmente tienes

=IF(IFERROR(SEARCH("Brand is not valid", M42), "") <> "", "Brand", "")

IFERRORes una gran función para mostrar una versión desinfectada de un valor calculado que podría ser un código de error; Lo uso y lo recomiendo en las respuestas de Superusuario con frecuencia. Como probablemente sabes,

• IFERROR(calculated_value, default_value)

es corto para

• IF(ISERROR(calculated_value), default_value, calculated_value)

Pero usarlo IFERRORpara crear una versión desinfectada de algún valor. y luego probar ese valor para hacer algo condicionalmente es una forma innecesariamente incómoda de utilizar IFERROR. La fórmula anterior se puede simplificar a

=IF(ISERROR(SEARCH("Brand is not valid", M42)), "", "Brand")

Y, como estoy seguro de que sabes, SEARCH("Brand is not valid", M42) las pruebas para ver siM42 contiene Brand is not valid. Pero, siempre que la columna Mpueda contener solo sus tres cadenas de error, esto se puede acortar a

=IF(ISERROR(SEARCH("Brand", M42)), "", "Brand")

o simplificado a

=IF(M42 = "Brand is not valid", "Brand", "")

---

Bien, ahora haré las Ofórmulas P, y Qun poco más complicadas:

• O42→=IF($A42=$A41, O41, "") & IF(ISERROR(SEARCH("Brand", $M42)), "", "Brand")
• P42→=IF($A42=$A41, P41, "") & IF(ISERROR(SEARCH("Product", $M42)), "", "Product")
• Q42→=IF($A42=$A41, Q41, "") & IF(ISERROR(SEARCH("OEM", $M42)), "", "OEM")

La fórmula para O42dice,

Si esta es la segunda o tercera fila para este ID (Columna A), mire la celda encima de ésta (es decir, la Ocelda de la columna de la fila anterior) para ver si ya hemos establecido que este objeto tiene una marca no válida. Además, mire la columna Mde esta fila para ver si es Brand is not valid. Luego concatene los resultados.

Dado que una ID única nunca aparecerá dos veces con el mismo error (¿verdad?), estos dos subresultados nunca estarán vacíos, por lo que esto es esencialmente hacer un "O":

Muestre un valor de Brandsi esta fila, O una de las filas anteriores para este ID, contiene el error de marca no válida.

Esto tiene el efecto de arrastrar los valores O, Py Qhasta la última fila para cada ID:

Tenga en cuenta que las filas 41, 44, 47 y 49 muestran cada una las formas breves de todos los errores que se aplican a sus respectivos ID en las columnas O, Py Q.

He definido Columna Rde la misma manera que tú. VerGenere una lista de contenidos de celda separados por comas, excluyendo los espacios en blanco para conocer técnicas para eliminar las barras no deseadas de esto.

Si tener la concatenación deseada sólo en las filas 41, 44, 47 y 49 es suficiente, ya está. En caso contrario, definir N42como

=IF($A22=$A23, N23, R22)

o

=IF($A22<>$A23, R22, N23)

Este es casi exactamente el mismo truco que usé en Columnas O, Py Q, pero en la dirección opuesta:

Si esta es la última fila para este ID (es decir, si es la fila 41, 44, 47 o 49), use la concatenación de los valores de esta fila (que es la colección completa de códigos de error para este ID). De lo contrario, mire la celda debajo de esta (es decir, la Ncelda de columna de la siguiente fila), que tendrá la respuesta correcta.

En otras palabras, los valores deseados se filtran hasta la primera fila para cada ID.