Fórmula do Excel / VBA para detectar uma propriedade de um grupo de linhas

Question

Eu tenho um grande conjunto de dados em uma planilha do Excel em que:

• coluna A: número de cotação
• coluna B: número da oportunidade
• coluna C: número do pedido de vendas.

Um número de cotação terá vários números de oportunidade. Um número de pedido de venda será gerado se uma oportunidade for fechada. O que preciso é uma fórmula que possa sinalizar uma cotação como status "fechado" quando todas as oportunidades relacionadas a essa cotação tiverem um número de pedido de venda. Por exemplo,

Column A | Column B | Column C
---------+----------+----------
  123    |  247689  | K123
  123    |  678889  | K456
  578    |  555678  | K889
  578    |  678999  |      

Para o exemplo acima, a cotação 123 deve ser marcada como "fechada".

    

Answer 1

Minha resposta é um pouco semelhante à de Doktoro Reichard. Minha resposta assume que seus dados são válidos (sem células em branco nas Colunas A ou B ) mas não que eles são classificados.

Usarei 2 e 1337 para representar a primeira e a última linha em que você tem dados. ajuste isso à sua situação. Na célula D2 digite

=SUMPRODUCT(--(A2=A$2:A$1337), --ISBLANK(C$2:C$1337))

Arraste / preencha isso para baixo na célula D1337 . Isso calculará o número de linhas que possuem o mesmo número de cotação (Coluna A ) como a linha atual e ter um número de pedido em branco (Coluna C ). Este será 0 para cotações fechadas e positivo para aspas abertas.

    

Answer 2

Use uma coluna auxiliar. Assumirei que seus dados estão classificados, pois isso facilitará meu trabalho:

A fórmula da primeira coluna auxiliar (aqui projetada como D ) é a seguinte:

=OR(IS.EMPTY(A1);IS.EMPTY(B1);IS.EMPTY(C1))

Isso retornará FALSE se o número da cotação, o número da oportunidade e o número do pedido de venda estiverem preenchidos. Isso retornará TRUE se um estiver vazio.

Com isso, você pode fazer um =OR em todas as células auxiliares que se referem ao mesmo número de cotação (é aqui que a classificação ajuda). Se todas as células auxiliares da respectiva cotação retornarem FALSE , o seguinte:

=IF(OR(D1:D2);"Open";"Closed")

irá escrever "Fechado" se todas as células forem FALSE .