Concatenar condicionalmente o conteúdo da célula entre linhas

Question

Eu tenho uma planilha com milhares de linhas que contêm um ID exclusivo Ae três códigos de erro diferentes M. Cada ID individual pode ter 1, 2 ou 3 erros. Eles estarão sempre na mesma ordem.

Digitei à mão Nqual é a saída desejada. Escrevi uma fórmula como esta em cols O, P, Qcom o texto do erro:

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

Tentei algo assim, Rmas não está funcionando corretamente por motivos óbvios. Existe uma maneira de fazer isso para que Rpareça N?

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

Se não for possível fazer isso com a fórmula do Excel, existe talvez uma maneira de VBA?

Answer 1

OK, levei algumas coçadas na cabeça, mas entendi:

Colunas O, P& Qcomo você as possui, intituladas Brand, Product& OEM. Transforme tudo em uma tabela com Ctrl- T(não é obrigatório, mas é útil, e minha coluna Rdepende disso, mas você pode usar referências de coluna se quiser)

Coluna R:

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

Coluna S:

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

Infelizmente, parece que a única maneira de usar referências de tabela para uma linha diferente é usar Offset, então, para simplificar, voltei às referências de células. Isso meio que nega o fator legal/prático de transformar tudo em uma mesa, mas, tanto faz...

E... Aqui está uma foto de como é:

Answer 2

Eu começaria otimizando suas Ofórmulas , Pe . QVocê tem atualmente

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

IFERRORé uma ótima função para exibir uma versão limpa de um valor computado que pode ser um código de erro; Eu uso e recomendo em respostas sobre superusuário, com frequência. Como você deve saber,

• IFERROR(calculated_value, default_value)

é curto para

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

Mas usar IFERRORpara criar uma versão higienizada de algum valor e então testar esse valor para fazer algo condicionalmente é uma maneira desnecessariamente estranha de usar IFERROR. A fórmula acima pode ser simplificada para

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

E, como tenho certeza que você sabe, SEARCH("Brand is not valid", M42) testes para ver seM42 contém Brand is not valid. Mas, desde que Column Mpossa conter apenas suas três strings de erro, isso pode ser abreviado para

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

ou simplificado para

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

---

OK, agora vou tornar as Ofórmulas P, e Qum pouco mais 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")

A fórmula para O42diz,

Se esta for a segunda ou terceira linha deste ID (Column A), observe a célula acima desta (ou seja, a Océlula Column da linha anterior) para ver se já estabelecemos que esta coisa tem uma marca inválida. Além disso, observe a coluna Mdesta linha para ver se é Brand is not valid. Em seguida, concatene os resultados.

Como um ID exclusivo nunca será listado duas vezes com o mesmo erro (certo?), esses dois subresultados nunca estarão vazios, então isso é essencialmente fazer um “OR”:

Mostre um valor Brandse esta linha, OU uma das linhas anteriores para este ID, contém o erro de marca inválida.

Isso tem o efeito de arrastar os valores O, Pe Qpara a última linha de cada ID:

Observe que as linhas 41, 44, 47 e 49 mostram, cada uma, as formas abreviadas de todos os erros que se aplicam aos seus respectivos IDs nas colunas O, Pe Q.

Eu defini Column Rda mesma maneira que você. VerGere uma lista separada por vírgulas do conteúdo das células, excluindo espaços em branco para técnicas para eliminar as barras indesejadas disso.

Se ter a concatenação desejada apenas nas linhas 41, 44, 47 e 49 for suficiente, pronto. Caso contrário, defina N42como

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

ou

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

Este é quase exatamente o mesmo truque que usei em Columns O, P, e Q, mas indo na direção oposta:

Se esta for a última linha deste ID (ou seja, se for a linha 41, 44, 47 ou 49), use a concatenação dos valores desta linha (que é a coleção completa de códigos de erro para este ID). Caso contrário, observe a célula abaixo desta (ou seja, a Ncélula Coluna da próxima linha), que terá a resposta correta.

Em outras palavras, os valores desejados percolam até a primeira linha de cada ID.